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:
9.4
9.5
10.0
10.1
10.2
10.3
10.4
10.5
11.0
11.1
Statistic
FMM
Blog
FM.SQL.JSONRecords
Returns rows as JSON array.
Component | Version | macOS | Windows | Linux | Server | FileMaker iOS SDK |
FM FMSQL JSON | 8.1 | Yes | Yes | Yes | Yes | Yes |
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. |
1+2 | Optional |
Result
Returns JSON array or error.
Description
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.
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
Blog Entries
- 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
FileMaker Magazin
Release notes
- 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.
Created 4th March 2018, last changed 2nd October 2020
FM.SQL.JSONRecord - FM.SQL.Max
Feedback: Report problem or ask question.

Links
MBS FileMaker Plugins