jsonToItems



Function: jsonToItems()

Converts a block of JSON data into a recordset which contains the attributes for each node.Ā 

Syntax

jsonToItems(JSON String, JSON Path, Lenient)

ArgumentTypeDescription
JSON StringStringJSON string to be converted to recordset.
JSON PathString

The JSON Path expression is evaluated against the data provided by the JSON String.Ā It determines which elements are extracted from the JSON.

Defaults toĀ ā€œ$ā€ which matches the entire document.Ā This must be passed in as a string i.e. encapsulated in quotes.

Lenientboolean

Lenient is a flag which determines whether to parse the JSON String leniently. Defaults to false.

Path Syntax

Expression Description

$ 

symbol refers to the root element.
@symbol refers to the current element.
. is the dot-child operator, which is used to denote a child element of the current element.
[ ] is used to select a child element of the current element (by name or index number).
*a wildcard, returning all elements regardless of their name.
^symbol is used to traverse up 1 element in the JSON hierarchy from child to parent. 

Examples

Record Set Returned

jsonToItems('{"data":[{"value":"foo"},{"status":"bar"}]}', '$.data', true)

Returns

// returns a record set
{"value" : "foo"}, {  "value" : "bar"}

Array Returned

jsonToItems('{"data":[{"value":"foo"},{"status":"bar"}]}', '$.data', true).value

Returns

// returns an array of values*
[foo, bar]

*The array results can be accessed using normalĀ Array Handling Functions