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)

• json Required. Complete JSON string

• path Required. 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:

Formula	Result
=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:

Formula	Result
=CS.SPARK_FILTERJSON(A1,"age")	23

You can also use multiple strings and match them to each other:

Formula	Result
=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.

=CS.SPARK_FILTERJSON(A1,"friends[*].name")

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"}}

Formula	Result
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}}

Formula	Result
=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}]}}

Formula	Result
=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}]}}

Formula	Result
=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}]}}

Formula	Result
=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}]}}

Formula	Filtered output
=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[0].key")	Key1
=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[1].key")	Key2

Sample file

21KB

CS.SPARK_FILTERJSON.xlsx

Open