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  

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.	"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

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

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

FM.SQL.JSONRecord - FM.SQL.Max

Feedback: Report problem or ask question.