Topics   All   MacOS (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Deprecated   Guides   Examples   Videos
New in version: 10.5   11.0   11.1   11.2   11.3   11.4   11.5   12.0   12.1   12.2    Statistic    FMM    Blog  


Returns rows as JSON array.

Component Version macOS Windows Linux Server iOS SDK License
FM FMSQL JSON 8.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes Paid
MBS( "FM.SQL.JSONRecords"; SQLref; FieldNames { ; Flags } )   More


Parameter Description Example Flags
SQLref The reference number returned by FM.SQL.Execute function. $SQLRef
FieldNames A list of field names for the JSON. "Model¶Names"
Flags The flags for the json creation.
Pass 1 to get all values as text.
Pass 2 to get all dates, times and timestamps in SQL format.
Pass 4 to get arrays instead of objects for the individual rows.
Pass 8 to get containers as objects with name, size and data entries. (new in v12.1)
1+2 Optional


Returns JSON array or error.


Returns rows as JSON array.
You provide list of field names, which should be in same order as fields in SQL result.
Optionally we can return SQL dates, times and timestamps as SQL format.
Containers are returned as Base64 encoded data.

See FM.SQL.XMLRecords for the same as XML.


Query JSON:

Set Variable [ $sql ; Value: MBS( "FM.SQL.Execute"; ""; "SELECT \"First\", \"Last\", Birthday, \"Photo Container\" FROM Contacts") ]
Set Variable [ $json ; Value: MBS( "FM.SQL.JSONRecords"; $sql; "First¶Last¶Birthday¶Photo"; 2) ]
Show Custom Dialog [ "JSON" ; $json ]
Set Variable [ $r ; Value: MBS( "FM.SQL.Release"; $sql ) ]

Make a JSON query in one Let statment:

Let (
        $sdat = "10/1/2019 00:00";
        $edat = "10/10/2019 00:00";
        sku = "'PARbh50','PARgy50'";
        sql1 = MBS("FM.SQL.Execute"; ""; "Select docnum,linetotal,db from \"MyTable\" where ItemCode in (" & sku & ") and DocDate between ? and ?"; $sdat; $edat);
        json = MBS( "FM.SQL.JSONRecords"; sql1; "DocNum¶LineTotal¶DB"; 1);
        r = MBS( "FM.SQL.Release"; sql1 )
    ] ; json

Query related records as JSON:

# get related teams
Set Variable [ $r ; Value: MBS( "FM.SQL.Execute"; Get(FileName); "SELECT \"UUID\", \"ID\", \"ID_Department\", \"Team.Name\", \"MemberCount\" FROM \"Teams\" WHERE ID_Department=?"; Department::ID) ]
If [ MBS("IsError") ]
    Show Custom Dialog [ "SQL error" ; $r ]
    # fill as json in field
    Set Field [ Department::JSON_Department ; MBS( "FM.SQL.JSONRecords"; $r; "UUID¶ID¶ID_Department¶Team.Name¶MemberCount") ]
    Set Variable [ $e ; Value: MBS( "FM.SQL.Release"; $r ) ]
End If

See also

Release notes

Blog Entries

Created 4th March 2018, last changed 12nd March 2022


Feedback: Report problem or ask question.

Start Chat