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

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

See also

• FM.SQL.Execute
• FM.SQL.JSONRecord
• FM.SQL.Release
• FM.SQL.XMLRecord
• FM.SQL.XMLRecords
• IsError
• SQL.Execute

Release notes

• 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 11.6pr3
• MBS FileMaker Plugin, version 11.2pr2
• MBS FileMaker Plugin, version 10.6pr3
• FileMaker records to XML or JSON
• Neues MBS FileMaker Plugin 10.2
• 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 1st May 2023

FM.SQL.JSONRecord - FM.SQL.List