FM.SQL.JSONRecords

Returns rows as JSON array.

Parameters

Parameter	Description	Example	Flags
SQLref	The reference number returned by FM.SQL.Execute function.	$SQLRef
FieldNames	A list of field names for the JSON. If you have duplicate entries in the field names, the resulting JSON will have duplicate keys, which can be a problem.	"Model¶Names"
Flags	The flags for the json creation. Pass 1 to get all values as text (empty text instead of null). Pass 2 to get all dates, times and timestamps in SQL format (formatted by plugin) instead of GetAsText() by FileMaker. 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
firstRow	Available in MBS FileMaker Plugin 14.2 or newer. The index of first row. Default is 0.	0	Optional
lastRow	Available in MBS FileMaker Plugin 14.2 or newer. The index of last row. Default is FM.SQL.RowCount-1.	5	Optional

Result

Returns JSON array or error.

Description

Examples

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 ]
Else
    # 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

Builds Assets as JSON with Assignments JSON array added:

# let us get Assets via SQL as JSON
Set Variable [ $sql1 ; Value: MBS( "FM.SQL.Execute"; Get(FileName); "SELECT * FROM Assets" ) ]
Set Variable [ $names1 ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); "SELECT FieldName FROM FileMaker_Fields WHERE TableName = 'Assets' "; 9; 13) ]
Set Variable [ $json1 ; Value: MBS( "FM.SQL.JSONRecords"; $sql1; $names1 ) ]
Set Variable [ $r ; Value: MBS( "FM.SQL.Release"; $sql1 ) ]
#
# let us get Assignments via SQL as JSON
Set Variable [ $sql2 ; Value: MBS( "FM.SQL.Execute"; Get(FileName); "SELECT * FROM Assignments" ) ]
Set Variable [ $names2 ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); "SELECT FieldName FROM FileMaker_Fields WHERE TableName = 'Assignments' "; 9; 13) ]
Set Variable [ $json2 ; Value: MBS( "FM.SQL.JSONRecords"; $sql2; $names2 ) ]
Set Variable [ $r ; Value: MBS( "FM.SQL.Release"; $sql2 ) ]
#
# how many Assets did we got?
Set Variable [ $count ; Value: MBS( "JSON.GetArraySize"; $json2) ]
#
# not go and add an Assignments Array to each Assets entry
Set Variable [ $index ; Value: 0 ]
If [ $index ≤ $count ]
    Loop [ Flush: Defer ]
        # your script steps here
        Set Variable [ $json1 ; Value: MBS( "JSON.SetPathItem"; $json1; $Index & ¶ & "Assignments"; "[]") ]
        # next
        Set Variable [ $index ; Value: $index + 1 ]
        Exit Loop If [ $index ≥ $count ]
    End Loop
End If
#
# now loop over all assignments and insert them into assets
Set Variable [ $index ; Value: 0 ]
If [ $index ≤ $count ]
    Loop [ Flush: Defer ]
        # your script steps here
        Set Variable [ $json3 ; Value: MBS( "JSON.GetArrayItem"; $json2; $index) ]
        Set Field [ JSON::JSON3 JSON::JSON3 ; $json3 $json3 ]
        # find the key to match
        Set Variable [ $key ; Value: MBS( "JSON.GetObjectItem"; $json3; "AssetForeignKey") ]
        # and lookup the index in the json array for this primary key
        Set Variable [ $DestIndex ; Value: MBS( "JSON.FindValueInObjectArray"; $json1; "PrimaryKey"; $key) ]
        If [ MBS("IsError") = 0 and $DestIndex ≥ 0 ]
            # add json to the array
            Set Variable [ $json1 ; Value: MBS( "JSON.SetPathItem"; $json1; $DestIndex & ¶ & "Assignments¶append"; $json3) ]
        End If
        #
        # next
        Set Variable [ $index ; Value: $index + 1 ]
        Exit Loop If [ $index ≥ $count ]
    End Loop
End If
# $json1 now has Assets records with Assignments as sub records in the JSON.

See also

• FM.SQL.JSONRecord
• FM.SQL.Release
• FM.SQL.RowCount
• FM.SQL.XMLRecord
• IsError
• JSON.GetArraySize
• JSON.InsertRecord
• JSON.ReplaceEvaluate
• JSON.SetPathItem
• SQL.Execute

Release notes

• Version 14.2
  • Added firstRow and lastRow parameter to FM.SQL.JSONRecords and FM.SQL.XMLRecords functions.
  • Improved FM.SQL.JSONRecords function to return empty array for empty result.
• Version 12.1
  • Added flag value 8 for FM.SQL.JSONColumn, Matrix.JSONRecord, Matrix.JSONRecords, FM.SQL.JSONRecord and FM.SQL.JSONRecords to return containers as JSON objects with data, size and name.
• Version 12.0
  • Fixed JSON functions to return NULL for numbers with no content, e.g. empty text. This affects JSON.CreateNumber, Dictionary.ToJSON, FM.SQL.JSONColumn, FM.SQL.JSONRecord and FM.SQL.JSONRecords functions.
• Version 11.2
  • Added flag 4 for FM.SQL.JSONRecords and FM.SQL.JSONRecord to return result as array of arrays instead of array of objects.
• Version 11.0
  • Fixed issue with FM.SQL.JSONRecord, FM.SQL.JSONRecords and Dictionary.ToJSON where long numbers where truncated to double precision.
• Version 8.1
  • Added FM.SQL.JSONRecord and FM.SQL.JSONRecords functions.

Blog Entries

• MBS FileMaker Plugin, version 14.2pr5
• JSON Replace with evaluate
• MBS FileMaker Plugin, version 14.2pr4
• MBS FileMaker Plugin, version 11.6pr3
• MBS FileMaker Plugin, version 11.2pr2
• MBS FileMaker Plugin, version 10.6pr3
• FileMaker records to XML or JSON
• MBS FileMaker Plugin 10.2 - More than 6200 Functions In One Plugin
• Query FileMaker records as JSON
• With JSON in excellent form

This function checks for a license.

Created 4th March 2018, last changed 6th May 2024

FM.SQL.JSONRecord - FM.SQL.List