FM.SQL.JSONRecord

Returns a row as JSON object.

Parameters

Parameter	Description	Example	Flags
SQLref	The reference number returned by FM.SQL.Execute function.	$SQLRef
Row	The row you like to get. 0 for first row.	1
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 text or error.

Description

Examples

Query JSON record:

# run query to get some data
Set Variable [ $sql ; Value: MBS( "FM.SQL.Execute"; ""; "SELECT \"First\", \"Last\", Company, \"Photo Container\" FROM Contacts") ]
# query data
Set Variable [ $json ; Value: MBS( "FM.SQL.JSONRecord"; $sql; 0; "First¶Last¶Company¶Photo"; 2) ]
# store result
Set Field [ Contacts::Output ; $json ]
# free memory
Set Variable [ $xml ; Value: MBS( "FM.SQL.Release"; $sql) ]

Query record as JSON:

Let ( [
fields = MBS( "FM.QueryFieldsForTableName"; "Contacts");
Records = MBS( "FM.SQL.Execute"; ""; "SELECT \"" & substitute(fields; ¶; "\", \"") & "\" FROM \"Contacts\" WHERE \"First\" = ?"; "Joe");

json = MBS( "FM.SQL.JSONRecord"; Records; 0; fields);
r = MBS( "FM.SQL.Release"; Records )]
; json )

Query current record as JSON:

Let([
// get field list
fields = MBS( "FM.QueryFieldsForTableName"; Get(LayoutTableName));
// query the fields using that fieold list for current record
sql = MBS( "FM.SQL.Execute"; Get(FileName); "SELECT \"" & Substitute(fields; ¶; "\", \"") & "\" FROM \"" & Get ( LayoutTableName ) & "\" WHERE RowID = ?"; Get( RecordID ));
// and query JSON
json = MBS( "FM.SQL.JSONRecord"; sql; 0; fields );
// free SQL result
R = MBS("FM.SQL.Release"; sql)
]; json)

See also

• FM.QueryFieldsForTableName
• FM.SQL.Execute
• FM.SQL.JSONRecords
• FM.SQL.Release
• FM.SQL.XMLRecord
• FM.SQL.XMLRecords
• JSON.InsertOrUpdateRecord
• JSON.InsertRecord
• JSON.ReplaceEvaluate
• 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.1
  • Fixed memory leak in FM.SQL.JSONRecord and Matrix.JSONRecord functions.
• Version 11.0
  • Fixed issue with FM.SQL.JSONRecord, FM.SQL.JSONRecords and Dictionary.ToJSON where long numbers where truncated to double precision.
• Version 9.0
  • Fixed problem with lost time value in FM.SQL.JSONRecord function result.
• 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 11.1pr2
• 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
• MBS FileMaker Plugin, version 8.6pr2

This function checks for a license.

Created 4th March 2018, last changed 1st May 2023

FM.SQL.JSONColumn - FM.SQL.JSONRecords