FILTERJSON
To make it easier to extract data from the Xcall response JSON, you can use the user-defined function Spark_FilterJSON. This function also works for parsing any other JSON string.
Syntax
CS.SPARK_FILTERJSON(json, path)
jsonRequired. Complete JSON stringpathRequired. The argument used to get the target data from [JSON]. JSONPath uses a standardized syntax.
Example
Copy the example JSON data below and paste it into the cell A1 of a new worksheet.
{
"name": "Chris",
"age": 23,
"address": {
"city": "New York",
"country": "America"
},
"friends": [
{
"name": "Emily",
"hobbies": [ "biking", "music", "gaming" ]
},
{
"name": "John",
"hobbies": [ "soccer", "gaming" ]
}
]
}Below are some examples usage of FILTERJSON:
=CS.SPARK_FILTERJSON(A1,"name")
Chris
=CS.SPARK_FILTERJSON(A1,"friends[0].name")
Emily
=CS.SPARK_FILTERJSON(A1,"friends[0].hobbies[2]")
gaming
JSONPath Syntax Guide
Hardcoded Direct Filtering
You can write the path directly into the formula:
=CS.SPARK_FILTERJSON(A1,"age")
23
You can also use multiple strings and match them to each other:
=CS.SPARK_FILTERJSON(A1,"friends"&"[0]&"name")
John
Column Filtering
Return key values of each object inside an array. Copy and paste the formula below to any cell.
This will result in a dynamic array representing all names inside the "friends" list.
Emily
John
Dynamic Filtering
JSON String: {"outputs": {"number_value": 1, "text_value": "text"}}
A2
=CS.SPARK_FILTERJSON(JSON_String,"outputs."&CHAR(34)&A1&CHAR(34))
1
B2
=CS.SPARK_FILTERJSON(JSON_String,"outputs."&CHAR(34)&B1&CHAR(34))
text
Please note that if you use a cell reference within the JSON path, the cell reference must be wrapped in CHAR(34). Cell references can be utilized in any of the following filtering methods as well.
Subservice Filtering
JSON String: {"outputs": {"number.value": 1}}
=CS.SPARK_FILTERJSON(JSON_String,"outputs."&"'number.value'")
1
This method can be used for any case where a parameter's key in the JSON string contains a period .. To prevent the period from interfering with the JSON path syntax, the parameter's key must be wrapped in single quotation marks.
Table Filtering (Dynamic Range)
JSON String: {"outputs": {"table": [{"key": "Key1", "value": 1}, {"key": "Key2", "value": 2}]}}
=CS.SPARK_FILTERJSON(JSON_String,"outputs.table")
If you define the JSON path down to an array, then CS.SPARK_FILTERJSON will return a dynamic range WITH the headers included. This means that the number of rows will automatically adjust to the number of data entries found within the JSON string.
Column Filtering (Dynamic Range)
JSON String: {"outputs": {"table": [{"key": "Key1", "value": 1}, {"key": "Key2", "value": 2}]}}
=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[*].key")
Please be aware that if you define the JSON path down to a column within a table array, then CS.SPARK_FILTERJSON will return a dynamic range WITHOUT the header included. It is recommended to hardcode the header row in this scenario and use cell references to the headers within the JSON path in the CS.SPARK_FILTERJSON formula.
Row filtering
JSON String: {"outputs": {"table": [{"key": "Key1", "value": 1}, {"key": "Key2", "value": 2}]}}
=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[0]")
{"key":"Key1","value":1}
=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[1]")
{"key":"Key2","value":2}
Please note that the arrays are indexed starting at 0. To parse the first row of data, you would define [0] in the JSON path. To parse the third row of data, you would define [2] in the JSON path.
Column & row filtering
JSON String: {"outputs": {"table": [{"key": "Key1", "value": 1}, {"key": "Key2", "value": 2}]}}
=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[0].key")
Key1
=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[1].key")
Key2
Sample file
Last updated