> For the complete documentation index, see [llms.txt](https://docs.coherent.global/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coherent.global/assistant/custom-functions/xcall-legacy-functions/filterjson.md).

# FILTERJSON

Syntax: `CS.SPARK_FILTERJSON(json, path)`

Extract data from a JSON string. This can be used to parse the `Xcall` response JSON.

| Parameter | Description                                                                                                         |
| --------- | ------------------------------------------------------------------------------------------------------------------- |
| `json` \* | Complete JSON string.                                                                                               |
| `path` \* | The argument used to get the target data from `[JSON]`. Uses standardized [JSONPath](https://jsonpath.com/) syntax. |

## Example

Copy the example JSON data below and paste it into the cell **A1** of a new worksheet.

```json
{
  "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`:

<table><thead><tr><th width="518">Formula</th><th>Result</th></tr></thead><tbody><tr><td><code>=CS.SPARK_FILTERJSON(A1,"name")</code></td><td><code>Chris</code></td></tr><tr><td><code>=CS.SPARK_FILTERJSON(A1,"friends[0].name")</code></td><td><code>Emily</code></td></tr><tr><td><code>=CS.SPARK_FILTERJSON(A1,"friends[0].hobbies[2]")</code></td><td><code>gaming</code></td></tr></tbody></table>

## JSONPath syntax guide

### Hardcoded direct filtering

You can write the path directly into the formula:

<table><thead><tr><th width="513">Formula</th><th>Result</th></tr></thead><tbody><tr><td><code>=CS.SPARK_FILTERJSON(A1,"age")</code></td><td><code>23</code></td></tr></tbody></table>

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

<table><thead><tr><th width="512">Formula</th><th>Result</th></tr></thead><tbody><tr><td><code>=CS.SPARK_FILTERJSON(A1,"friends"&#x26;"[0]&#x26;"name")</code></td><td><code>John</code></td></tr></tbody></table>

### Column filtering

Return key values of each object inside an array. Copy and paste the formula below to any cell.

```excel-formula
=CS.SPARK_FILTERJSON(A1,"friends[*].name")
```

This will result in a dynamic array representing all names inside the "`friends`" list.

<table data-header-hidden><thead><tr><th></th><th data-hidden></th></tr></thead><tbody><tr><td><code>Emily</code></td><td></td></tr><tr><td><code>John</code></td><td></td></tr></tbody></table>

### Dynamic filtering

JSON String: `{"outputs": {"number_value": 1, "text_value": "text"}}`

<table data-full-width="false"><thead><tr><th>Formula</th><th>Result</th></tr></thead><tbody><tr><td><p><code>A2</code></p><p><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs."&#x26;CHAR(34)&#x26;A1&#x26;CHAR(34))</code></p></td><td><code>1</code></td></tr><tr><td><p><code>B2</code></p><p><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs."&#x26;CHAR(34)&#x26;B1&#x26;CHAR(34))</code></p></td><td><code>text</code></td></tr></tbody></table>

{% hint style="warning" %}
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.
{% endhint %}

### Subservice filtering

JSON String: `{"outputs": {"number.value": 1}}`

<table data-full-width="false"><thead><tr><th>Formula</th><th>Result</th></tr></thead><tbody><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs."&#x26;"'number.value'")</code></td><td><code>1</code></td></tr></tbody></table>

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}]}}`

<table data-full-width="false"><thead><tr><th>Formula</th><th>Result</th></tr></thead><tbody><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs.table")</code></td><td></td></tr></tbody></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}]}}`

<table data-full-width="false"><thead><tr><th>Formula</th><th>Result</th></tr></thead><tbody><tr><td>=<code>CS.SPARK_FILTERJSON(JSON_String,"outputs.table[*].key")</code></td><td></td></tr></tbody></table>

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}]}}`

<table data-full-width="false"><thead><tr><th>Formula</th><th>Result</th></tr></thead><tbody><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[0]")</code></td><td><code>{"key":"Key1","value":1}</code></td></tr><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[1]")</code></td><td><code>{"key":"Key2","value":2}</code></td></tr></tbody></table>

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 and row filtering

JSON String: `{"outputs": {"table": [{"key": "Key1", "value": 1}, {"key": "Key2", "value": 2}]}}`

<table data-full-width="false"><thead><tr><th>Formula</th><th>Filtered output</th></tr></thead><tbody><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[0].key")</code></td><td><code>Key1</code></td></tr><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[1].key")</code></td><td><code>Key2</code></td></tr></tbody></table>

## Sample file

{% file src="/files/ghpUaEyS7qttfGdmaKL0" %}