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.4
10.5
11.0
11.1
11.2
11.3
11.4
11.5
12.0
12.1
Statistic
FMM
Blog
FM.SQL.JSONRecord
Returns a row as JSON object.
| Component | Version | macOS | Windows | Linux | Server | 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 | |
| Row | The row you like to get. 0 for first row. |
1 | |
| 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. |
1+2 | Optional |
Result
Returns JSON text or error.
Description
Returns a row as JSON object.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.XMLRecord for the same as XML.
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 ; $xml ]
# 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 )
See also
- FM.QueryFieldsForTableName
- FM.SQL.Execute
- FM.SQL.JSONRecords
- FM.SQL.Release
- FM.SQL.XMLRecord
- FM.SQL.XMLRecords
- SQL.Execute
Release notes
- 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
- MBS FileMaker Plugin, version 8.6pr2
FileMaker Magazin
Created 4th March 2018, last changed 9th November 2021
FM.SQL.JSONColumn - FM.SQL.JSONRecords
Feedback: Report problem or ask question.
Links
MBS FileMaker blog
