> 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)`

* `json` Required. Complete JSON string
* `path` Required. The argument used to get the target data from \[JSON]. [JSONPath](https://jsonpath.com/) uses a standardized 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"}}`

<figure><img src="/files/Hhqctr1oANc3oktpdELN" alt=""><figcaption></figcaption></figure>

<table data-full-width="false"><thead><tr><th>Formula</th><th align="center">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 align="center"><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 align="center"><code>text</code></td></tr></tbody></table>

{% hint style="danger" %}
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 align="center">Result</th></tr></thead><tbody><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs."&#x26;"'number.value'")</code></td><td align="center">1</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 align="center">Result</th></tr></thead><tbody><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs.table")</code></td><td align="center"><img src="/files/tjzXq159ejdyzWzidwVk" alt=""></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 align="center">Result</th></tr></thead><tbody><tr><td>=<code>CS.SPARK_FILTERJSON(JSON_String,"outputs.table[*].key")</code></td><td align="center"><img src="/files/SCxGOWlOjOpHDfthTWMU" alt=""></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 align="center">Result</th></tr></thead><tbody><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[0]")</code></td><td align="center"><code>{"key":"Key1","value":1}</code></td></tr><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[1]")</code></td><td align="center"><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 & 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 align="center">Filtered output</th></tr></thead><tbody><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[0].key")</code></td><td align="center"><code>Key1</code></td></tr><tr><td><code>=CS.SPARK_FILTERJSON(JSON_String,"outputs.table[1].key")</code></td><td align="center"><code>Key2</code></td></tr></tbody></table>

## Sample file

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


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.coherent.global/assistant/custom-functions/xcall-legacy-functions/filterjson.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.