FM.SQL.JSONRecords
------------------

Returns rows as JSON array.

| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
|---|---|---|---|---|---|---|
| [FM](component_FM.md) [FMSQL](component_FMSQL.md) [JSON](component_JSON.md) | [8.1](newinversion81.md) | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |

MBS( "FM.SQL.JSONRecords"; SQLref; FieldNames { ; Flags; FirstRow; LastRow } ) 

**MBS**( **"FM.SQL.JSONRecords";** /\* Returns rows as JSON array. \*/  
**$SQLref**; /\* The reference number returned by [FM.SQL.Execute](FMSQLExecute.md) function. \*/   
**$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.e.g. "Model¶Names" \*/   
**$Flags**; /\* Optional; The flags for the json creation.  
Add 1 to get all values as text (empty text instead of null).  
Add 2 to get all dates, times and timestamps in SQL format (formatted by plugin) instead of GetAsText() by FileMaker.  
Add 4 to get arrays instead of objects for the individual rows.  
Add 8 to get containers as objects with name, size and data entries. (new in v12.1)  
Add 32 to detect JSON in text values and return the JSON inline. (New in v15.4)e.g. 1+2 \*/   
**$FirstRow**; /\* Optional; The index of first row.  
Default is 0.e.g. 0 \*/   
**$LastRow**) /\* Optional; The index of last row.  
Default is [FM.SQL.RowCount](FMSQLRowCount.md)-1.e.g. 5 \*/ 

### Parameters

| Parameter | Description | Example | Flags |
|---|---|---|---|
| SQLref | The reference number returned by [FM.SQL.Execute](FMSQLExecute.md) 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.   Add 1 to get all values as text (empty text instead of null).   Add 2 to get all dates, times and timestamps in SQL format (formatted by plugin) instead of GetAsText() by FileMaker.   Add 4 to get arrays instead of objects for the individual rows.   Add 8 to get containers as objects with name, size and data entries. (new in v12.1)   Add 32 to detect JSON in text values and return the JSON inline. (New in v15.4) | 1+2 | Optional |
| FirstRow | The index of first row.   Default is 0. | 0 | Optional      Added in version **14.2**. |
| LastRow | The index of last row.   Default is [FM.SQL.RowCount](FMSQLRowCount.md)-1. | 5 | Optional      Added in version **14.2**. |

### 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](FMSQLXMLRecords.md) for the same as XML.  
### Examples

Query JSON:

 Set Variable \[ $sql ; Value: MBS ( "[FM.SQL.Execute](FMSQLExecute.md)"; ""; "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](FMSQLRelease.md)"; $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](FMSQLExecute.md)"; ""; "Select docnum,linetotal,db from \\"MyTable\\" where ItemCode in (" &amp; sku &amp; ") and DocDate between ? and ?"; $sdat ; $edat );  
 json = MBS ( "FM.SQL.JSONRecords"; sql1; "DocNum¶LineTotal¶DB"; 1);  
 r = MBS ( "[FM.SQL.Release](FMSQLRelease.md)"; sql1 )  
 \] ; json  
)  
Query related records as JSON:

 # get related teams   
Set Variable \[ $r ; Value: MBS ( "[FM.SQL.Execute](FMSQLExecute.md)"; 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](FMSQLRelease.md)"; $r ) \]   
End If  
Builds Assets as JSON with Assignments JSON array added:

 # let us get Assets via SQL as JSON   
Set Variable \[ $sql1 ; Value: MBS ( "[FM.SQL.Execute](FMSQLExecute.md)"; Get(FileName); "SELECT \* FROM Assets" ) \]  
Set Variable \[ $names1 ; Value: MBS ( "[FM.ExecuteFileSQL](FMExecuteFileSQL.md)"; Get(FileName); "SELECT FieldName FROM FileMaker\_Fields WHERE TableName = 'Assets' "; 9; 13) \]  
Set Variable \[ $json1 ; Value: MBS ( "FM.SQL.JSONRecords"; $sql1 ; $names1 ) \]  
Set Variable \[ $r ; Value: MBS ( "[FM.SQL.Release](FMSQLRelease.md)"; $sql1 ) \]  
\#   
\# let us get Assignments via SQL as JSON   
Set Variable \[ $sql2 ; Value: MBS ( "[FM.SQL.Execute](FMSQLExecute.md)"; Get(FileName); "SELECT \* FROM Assignments" ) \]  
Set Variable \[ $names2 ; Value: MBS ( "[FM.ExecuteFileSQL](FMExecuteFileSQL.md)"; Get(FileName); "SELECT FieldName FROM FileMaker\_Fields WHERE TableName = 'Assignments' "; 9; 13) \]  
Set Variable \[ $json2 ; Value: MBS ( "FM.SQL.JSONRecords"; $sql2 ; $names2 ) \]  
Set Variable \[ $r ; Value: MBS ( "[FM.SQL.Release](FMSQLRelease.md)"; $sql2 ) \]  
\#   
\# how many Assets did we got?   
Set Variable \[ $count ; Value: MBS ( "[JSON.GetArraySize](JSONGetArraySize.md)"; $json2 ) \]  
\#   
\# not go and add an Assignments Array to each Assets entry   
Set Variable \[ $index ; Value: 0 \]  
If \[ $index ≤ $count \]  
 Loop \[ Flush: Defer \]  
 # your script steps here  
 Set Variable \[ $json1 ; Value: MBS ( "[JSON.SetPathItem](JSONSetPathItem.md)"; $json1 ; $Index &amp; ¶ &amp; "Assignments"; "\[\]") \]  
 # next  
 Set Variable \[ $index ; Value: $index + 1 \]  
 Exit Loop If \[ $index ≥ $count \]  
 End Loop  
End If  
\#   
\# now loop over all assignments and insert them into assets   
Set Variable \[ $index ; Value: 0 \]  
If \[ $index ≤ $count \]  
 Loop \[ Flush: Defer \]  
 # your script steps here  
 Set Variable \[ $json3 ; Value: MBS ( "[JSON.GetArrayItem](JSONGetArrayItem.md)"; $json2 ; $index ) \]  
 Set Field \[ JSON::JSON3 ; $json3 \]  
 # find the key to match  
 Set Variable \[ $key ; Value: MBS ( "[JSON.GetObjectItem](JSONGetObjectItem.md)"; $json3 ; "AssetForeignKey") \]  
 # and lookup the index in the json array for this primary key  
 Set Variable \[ $DestIndex ; Value: MBS ( "[JSON.FindValueInObjectArray](JSONFindValueInObjectArray.md)"; $json1 ; "PrimaryKey"; $key ) \]  
 If \[ MBS ("IsError") = 0 and $DestIndex ≥ 0 \]  
 # add json to the array  
 Set Variable \[ $json1 ; Value: MBS ( "[JSON.SetPathItem](JSONSetPathItem.md)"; $json1 ; $DestIndex &amp; ¶ &amp; "Assignments¶append"; $json3 ) \]  
 End If  
 #   
 # next  
 Set Variable \[ $index ; Value: $index + 1 \]  
 Exit Loop If \[ $index ≥ $count \]  
 End Loop  
End If  
\# $json1 now has Assets records with Assignments as sub records in the JSON.   
Query value lists via SQL in FileMaker 22:

 Let(\[  
 sql = MBS ("[FM.SQL.Execute](FMSQLExecute.md)"; ""; "SELECT ValueListName, ValueListId, Source, ModCount FROM FileMaker\_ValueLists");  
 json = MBS ( "FM.SQL.JSONRecords"; sql; "ValueListName¶ValueListId¶Source¶ModCount" );  
 r = MBS ("[FM.SQL.Release](FMSQLRelease.md)"; sql)  
\]; json)  
Query values of a value list via SQL:

 Let(\[  
 sql = MBS ("[FM.SQL.Execute](FMSQLExecute.md)"; ""; "SELECT \* FROM FileMaker\_ValueList\_Test");  
 json = MBS ( "FM.SQL.JSONRecords"; sql; "Value¶SecondValue" );  
 r = MBS ("[FM.SQL.Release](FMSQLRelease.md)"; sql)  
\]; json)  
<small>  
Example result:   
\[ { "Value" : "a" , "SecondValue" : "Hello" }, { "Value" : "b" , "SecondValue" : "World" } \] </small>### See also

- [FM.SQL.JSONRecord](FMSQLJSONRecord.md)
- [FM.SQL.RowCount](FMSQLRowCount.md)
- [FM.SQL.XMLRecords](FMSQLXMLRecords.md)
- [IsError](IsError.md)
- [JSON.FindValueInObjectArray](JSONFindValueInObjectArray.md)
- [JSON.GetArrayItem](JSONGetArrayItem.md)
- [JSON.GetArraySize](JSONGetArraySize.md)
- [JSON.InsertOrUpdateRecord](JSONInsertOrUpdateRecord.md)
- [JSON.SetPathItem](JSONSetPathItem.md)
- [SQL.Execute](SQLExecute.md)

### Release notes

- **Version 15.4**
    - Added DetectJSON flag to [FM.SQL.JSONColumn](https://www.mbsplugins.eu/FMSQLJSONColumn.shtml), [FM.SQL.JSONRecord](https://www.mbsplugins.eu/FMSQLJSONRecord.shtml) and [FM.SQL.JSONRecords](https://www.mbsplugins.eu/FMSQLJSONRecords.shtml).
- **Version 14.2**
    - Added firstRow and lastRow parameter to [FM.SQL.JSONRecords](https://www.mbsplugins.eu/FMSQLJSONRecords.shtml) and [FM.SQL.XMLRecords](https://www.mbsplugins.eu/FMSQLXMLRecords.shtml) functions.
    - Improved [FM.SQL.JSONRecords](https://www.mbsplugins.eu/FMSQLJSONRecords.shtml) function to return empty array for empty result.
- **Version 12.1**
    - Added flag value 8 for [FM.SQL.JSONColumn](https://www.mbsplugins.eu/FMSQLJSONColumn.shtml), [Matrix.JSONRecord](https://www.mbsplugins.eu/MatrixJSONRecord.shtml), [Matrix.JSONRecords](https://www.mbsplugins.eu/MatrixJSONRecords.shtml), [FM.SQL.JSONRecord](https://www.mbsplugins.eu/FMSQLJSONRecord.shtml) and [FM.SQL.JSONRecords](https://www.mbsplugins.eu/FMSQLJSONRecords.shtml) to return containers as JSON objects with data, size and name.
- **Version 12.0**
    - Fixed [JSON](https://www.mbsplugins.eu/component_JSON.shtml) functions to return NULL for numbers with no content, e.g. empty text. This affects [JSON.CreateNumber](https://www.mbsplugins.eu/JSONCreateNumber.shtml), [Dictionary.ToJSON](https://www.mbsplugins.eu/DictionaryToJSON.shtml), [FM.SQL.JSONColumn](https://www.mbsplugins.eu/FMSQLJSONColumn.shtml), [FM.SQL.JSONRecord](https://www.mbsplugins.eu/FMSQLJSONRecord.shtml) and [FM.SQL.JSONRecords](https://www.mbsplugins.eu/FMSQLJSONRecords.shtml) functions.
- **Version 11.2**
    - Added flag 4 for [FM.SQL.JSONRecords](https://www.mbsplugins.eu/FMSQLJSONRecords.shtml) and [FM.SQL.JSONRecord](https://www.mbsplugins.eu/FMSQLJSONRecord.shtml) to return result as array of arrays instead of array of objects.
- **Version 11.0**
    - Fixed issue with [FM.SQL.JSONRecord](https://www.mbsplugins.eu/FMSQLJSONRecord.shtml), [FM.SQL.JSONRecords](https://www.mbsplugins.eu/FMSQLJSONRecords.shtml) and [Dictionary.ToJSON](https://www.mbsplugins.eu/DictionaryToJSON.shtml) where long numbers where truncated to double precision.
- **Version 8.1**
    - Added [FM.SQL.JSONRecord](http://www.mbsplugins.eu/FMSQLJSONRecord.shtml) and [FM.SQL.JSONRecords](http://www.mbsplugins.eu/FMSQLJSONRecords.shtml) functions.

### Blog Entries

- [MBS FileMaker Advent calendar - Door 14 - SQL and JSON](https://www.mbsplugins.de/archive/2025-12-14/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Plugin, version 15.4pr1](https://www.mbsplugins.de/archive/2025-08-01/MBS_FileMaker_Plugin_version_1/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Plugin, version 14.2pr5](https://www.mbsplugins.de/archive/2024-05-08/MBS_FileMaker_Plugin_version_1/monkeybreadsoftware_blog_filemaker)
- [JSON Replace with evaluate](https://www.mbsplugins.de/archive/2024-05-06/JSON_Replace_with_evaluate/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Plugin, version 14.2pr4](https://www.mbsplugins.de/archive/2024-05-02/MBS_FileMaker_Plugin_version_1/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Plugin, version 11.6pr3](https://www.mbsplugins.de/archive/2021-12-14/MBS_FileMaker_Plugin_version_1/monkeybreadsoftware_blog_filemaker)
- [FileMaker records to XML or JSON](https://www.mbsplugins.de/archive/2020-06-30/FileMaker_records_to_XML_or_JS/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Plugin 10.2 - More than 6200 Functions In One Plugin](https://www.mbsplugins.de/archive/2020-05-12/MBS_FileMaker_Plugin_102_-_Mor/monkeybreadsoftware_blog_filemaker)
- [Query FileMaker records as JSON](https://www.mbsplugins.de/archive/2020-04-03/Query_FileMaker_records_as_JSO/monkeybreadsoftware_blog_filemaker)
- [With JSON in excellent form](https://www.mbsplugins.de/archive/2019-11-16/With_JSON_in_excellent_form/monkeybreadsoftware_blog_filemaker)

### FileMaker Magazin

- [Ausgabe 3/2018, Seite 34](https://filemaker-magazin.de/neuigkeit/3953-Appetithappen-FMM_201803)

This function checks for a license.

Created 4th March 2018 , last changed 5th September 2025

  
[FM.SQL.JSONRecord](FMSQLJSONRecord.md) - [FM.SQL.List](FMSQLList.md)

[HTML Version](FMSQLJSONRecords.shtml)