Has JSON data types

Working with the JSON functions

FileMaker Pro provides several text features that your solution can use to parse and modify JSON data from other sources, such as: B. from web services that transfer data in JSON format via a REST API.

For more information on the JSON data format, visit json.org.

Retrieving JSON data from a web service

Use the Insert From URL script step to access a web service, specify parameters for the information to retrieve, send and receive HTTP headers, and save the results in a variable or field.

Example: A bakery makes its product list available to customers in JSON format via a REST API. The following returns the list of today's offers as JSON data in the $$ JSON variable:

Set variable [$ url; "https: // / rest / api / products"]

Paste from URL [verify SSL certificates; Selection; With dialogue: Off; $$ JSON;
$ url; "--data list = Offers"]

For the data returned in $$ JSON, see Example JSON Data.

FileMaker Pro also provides several helper functions for dealing with the character encoding and cryptographic signing that some REST APIs require:

• HexDecode function

• HexEncode function

• TextDecode function

• TextEncode function

• Base64Decode function

• Base64EncodeRFC function

• CryptAuthCode function

• CryptDigest function

Formatting JSON data

JSON data does not require spaces or line endings between elements. However, to make the data easier to read when troubleshooting problems, use the JSONFormatElements function, which adds tabs and end-of-line characters, as shown in the JSON data example.

Parsing JSON data

Use the following JSON functions to parse JSON data; H. Get keys, values, or entire JSON objects or arrays that you can process further:

• JSONGetElement - Queries JSON data for an element (object, array, or value)

• JSONListKeys - Lists object names (keys) or array indexes in JSON data

• JSONListValues ​​- Lists the values ​​in JSON data

The first parameter of these functions, json, specifies the text field, variable or text expression with the valid JSON data for editing.

The second parameter, key or index or path, specifies the part of the JSON data to be processed:

• Key - the name of a key in a JSON object

• Index - index of an element in a JSON array (the first element has index 0)

• Path - hierarchical character string made up of key names or array indices

The following syntax is supported for the keyOrIndexOrPath parameter.

 

Parameter KeyOrIndexOrPath

Explanation

"."

(Optional) The root level, if it is the first character

". [n]"

Elements at index n of an array at the root level

".Surname"

The key of an object named name at the root level

".nameA.nameB.nameC"

An object named nameC that is a descendant of nameB and nameA

". [3] [2] [1] nameA [0]"

The first element of the array in the nameA object, which is on the third level of a group of nested arrays

The following example uses JSONListKeys to determine the number of products in the sample JSON data (stored in the $$ JSON variable), creates a record for each product, and sets fields in each record to the values ​​obtained using JSONGetElement for every product can be retrieved.

Set variable [$ product number; Value:
ElementsNumber (
JSONListKeys ($$ JSON; "Bakery.Product")
   ) ]
Set variable [$ i; Value: 0]
If [$ product number> 0]
Loop (beginning)
New data record / query
Set field value [Products :: ID;
JSONGetElement ($$ JSON; "Bakery.Product [" & $ i & "] ID")]
Set field value [Products :: Price;
JSONGetElement ($$ JSON; "Bakery.Product [" & $ i & "] Price")]
Set field value [Products :: Stock;
JSONGetElement ($$ JSON; "Bakery.Product [" & $ i & "] Stock")]
Write change data / request [With dialog: Off]
Set variable [$ i; Value: $ i + 1]
Exit loop if [$ i ≥ $ ProductNumber]
Loop (end)
End (if)

Modifying and adding JSON data elements

Use the JSONSetElement function to change values ​​and add elements in JSON data. The parameters json and keyOrIndexOrPath work here as described under Parsing JSON data. If keyOrIndexOrPath indicates an existing element, the value of that element is changed. If the element does not exist, it will be added.

JSONSetElement sets the specified element to the value parameter. You can specify any valid JSON value, from a simple string or number to a complex object or array.

The type parameter specifies the data type in value so that the JSON parser follows strict rules when dealing with each data type. For information about the supported data types, see JSONSetElement function. To insert JSON data into json, you can set type to JSONRaw and let the JSON parser determine the data type of value.

The following example adds the key-value pairs for a new product to an empty JSON object. The new object is then added to the end of the product array in the $$ JSON variable (see example for JSON data).

Set variable [$ NewProduct; Value:
JSONSetElement ("{}";
["ID"; "FB4"; JSONString];
[ "Surname" ; "Vanilla cake" ; JSONString];
["Price"; 17.5; JSONNumber];
[ "Duration" ; 12; JSONNumber];
["Category"; "Cake" ; JSONString];
[ "Offer" ; true; JSONBoolean]
   ) ]
Set variable [$ NextIndex; Value:
ElementsNumber (
JSONListKeys ($$ JSON; "Bakery.Product")
   ) ]
Set variable [$$ JSON; Value:
JSONSetElement (
$$ JSON; "Bakery.Product [" & $ NextIndex & "]"; $ NewProduct;
JSONObject
   ) ]

Deletion of JSON data elements

Use the JSONDeleteElement function to delete an element. The parameters json and keyOrIndexOrPath work here as described under Parsing JSON data. The keyOrIndexOrPath parameter must specify an existing element in json.

The following example deletes the element in the product array whose "ID" key in the $$ JSON variable has the value FB3 (see example for JSON data).

Set variable [$ product number; Value:
ElementsNumber (
JSONListKeys ($$ JSON; "Bakery.Product")
   ) ]
Set variable [$ i; Value: 0]
If [$ product number> 0]
Loop (beginning)
Set variable [$ ID; Value:
JSONGetElement ($$ JSON; "Bakery.Product [" & $ i & "] ID")]
If [$ ID = "FB3"]
Set variable [$$ JSON; Value:
JSONDeleteElement ($$ JSON; "Bakery.Product [" & $ i & "]")]
Exit current script [text result: 0]
End (if)
Set variable [$ i; Value: $ i + 1]
Exit loop if [$ i ≥ $ ProductNumber]
Loop (end)
End (if)

Handling errors in JSON data

If an error occurs while parsing the json parameter, the JSON function returns a "?" Followed by an error message from the JSON parser. For example, if the ":" is missing after the "Bakery" key in Example for JSON data, this formula returns

JSONGetElement ($$ JSON; "Bakery.Product [0] id")

this error message back:

? * Line 3, Column 2
Missing ':' after object member name
* Line 13, Column 5
Extra non-whitespace after JSON value.

The JSONFormatElements function can be used to determine whether JSON data is valid and to test whether the first character is a "?". Example:

Set variable [$ result; Value: JSONFormatElement ($$ JSON)]
If [Left ($ Result; 1) = "?" ]
# $$ JSON contains invalid JSON data.
End (if)

Example of JSON data

In the following example, JSON data contains a “bakery” object with an array of three “product” objects, each with multiple key-value pairs.

{
"Bakery":
{
"Product":
[
{
"ID": "FB1",
"Name" "Donuts",
"Price": 1.99;
"Stock": 43;
"Category": "Bread",
"Offer": true
},
{
"ID": "FB2",
"Price": 22.5;
"Name" "Chocolate Cake",
"Stock": 23;
"Category": "Cake",
"Offer": true
},
{
"ID": "FB3",
"Price": 3.95;
"Name" "Baguette",
"Stock": 34;
"Category": "Bread",
"Offer": true
}
]
}
}

Hints

• The JSON parser preserves the order of the elements in an array, but not the order of the elements in an object. As a result, the JSON functions can return elements in an object in an order other than that specified.

Further topics

Text functions

Container functions