> For the complete documentation index, see [llms.txt](https://cortex-docs.paloaltonetworks.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/json_path_extract.md).

# json\_path\_extract

Use the `json_path_extract()` function to retrieve values from a JSON string based on a specified JSONPath expression.

## Syntax

```sql
json_path_extract (<json_field>, <json_path>)
```

## Parameters

| Name         | Type   | Required | Description                                                      |
| ------------ | ------ | -------- | ---------------------------------------------------------------- |
| `json_field` | string | Yes      | The field containing the JSON string from which to extract data. |
| `json_path`  | string | Yes      | The JSONPath expression identifying the data to extract.         |

## Returns

The `json_path_extract()` function returns a string representation of the extracted JSON data.

## Usage notes

* This function supports a syntactic sugar format for simplified query writing: `<json_field> ->-> "<json_path>"`.
* When using the regular syntax, the `<json_path>` must be enclosed in double quotes, and the root of the JSON object is represented by a dollar sign (`$`). The `$` is not required when using the syntactic sugar format.
* If a field name within the `<json_path>` contains special characters such as a dot (`.`) or colon (`:`), specific syntax is required:
* Regular Syntax: Enclose the field name in single quotes within brackets (for example, `['<json_field>']`).
* Syntactic Sugar Format: Enclose the field name in double quotes within brackets (for example, `["<json_field>"]`).
* JSON field names are case-sensitive. The key-to-field pairing in the XQL query must be identical to the JSON structure for results to be found.
* This function performs a "very heavy" operation and requires significant resources to run. Use it when standard dot-notation or simple array indexing is insufficient for your data extraction needs.
* The syntax used corresponds to the standard found in the JavaScript `jsonpath` package.

## Examples

### Example 1: Extracting all authors from a nested array

**Goal**: Extract all `author` values from the `ServerAccessConfig` array within the `Firewall` object using the `[*]` wildcard.

**XQL code**:

```sql
config timeframe = 1d
| dataset = sample_xql_raw
| limit 1
| alter json_field = "{\"Firewall\":{\"ServerAccessConfig\":[{\"category\":\"policy\",\"author\":\"NRees\",\"name\":\"CustomerSuccess_NoAccess\",\"priority\":8.95},{\"category\":\"rule\",\"author\":\"EWaugh\",\"name\":\"AllowAccess_10_10_10_10\",\"id\":\"0-553-21311-3\",\"priority\":12.99},{\"category\":\"policy\",\"author\":\"HMelville\",\"name\":\"SOC_Access\",\"priority\":8.99},{\"category\":\"rule\",\"author\":\"JTolkien\",\"name\":\"AllowAccess_JIT\",\"id\":\"0-395-19395-8\",\"priority\":22.99}],\"Reviewer\":{\"UserName\":\"jdow\",\"Role\":\"Admin\"}}}"
| alter all_authors = json_path_extract(json_field, "$.Firewall.ServerAccessConfig[*].author")
| fields event_id, all_authors
```

**Explanation**: The query defines a static JSON object and uses the `[*]` operator to iterate through all elements of the `ServerAccessConfig` array, extracting the `author` field from each.

**Output**:

| EVENT\_ID | ALL\_AUTHORS                               |
| --------- | ------------------------------------------ |
| 101       | \["NRees","EWaugh","HMelville","JTolkien"] |

### Example 2: Extracting authors with a specific priority

**Goal**: Use a filter expression within the JSONPath to find authors associated with a specific priority value.

**XQL code**:

```sql
config timeframe = 1d
| dataset = sample_xql_raw
| limit 1
| alter json_field = "{\"Firewall\":{\"ServerAccessConfig\":[{\"category\":\"policy\",\"author\":\"NRees\",\"name\":\"CustomerSuccess_NoAccess\",\"priority\":8.95},{\"category\":\"rule\",\"author\":\"EWaugh\",\"name\":\"AllowAccess_10_10_10_10\",\"id\":\"0-553-21311-3\",\"priority\":12.99},{\"category\":\"policy\",\"author\":\"HMelville\",\"name\":\"SOC_Access\",\"priority\":8.99},{\"category\":\"rule\",\"author\":\"JTolkien\",\"name\":\"AllowAccess_JIT\",\"id\":\"0-395-19395-8\",\"priority\":22.99}],\"Reviewer\":{\"UserName\":\"jdow\",\"Role\":\"Admin\"}}}"
| alter specific_priority_author = json_path_extract(json_field, "$..*[?(@.priority==22.99)].author")
| fields event_id, specific_priority_author
```

**Explanation**: The query utilizes the filter expression `?(@.priority==22.99)` to match objects where the `priority` is exactly 22.99, and then extracts the `author` from those matching objects.

**Output**:

| EVENT\_ID | SPECIFIC\_PRIORITY\_AUTHOR |
| --------- | -------------------------- |
| 101       | \["JTolkien"]              |

### Example 3: Deep scan for all occurrences of a key

**Goal**: Use the recursive descent operator to find all `author` values anywhere in the JSON structure.

**XQL code**:

```sql
config timeframe = 1d
| dataset = sample_xql_raw
| limit 1
| alter json_field = "{\"Firewall\":{\"ServerAccessConfig\":[{\"category\":\"policy\",\"author\":\"NRees\",\"name\":\"CustomerSuccess_NoAccess\",\"priority\":8.95},{\"category\":\"rule\",\"author\":\"EWaugh\",\"name\":\"AllowAccess_10_10_10_10\",\"id\":\"0-553-21311-3\",\"priority\":12.99},{\"category\":\"policy\",\"author\":\"HMelville\",\"name\":\"SOC_Access\",\"priority\":8.99},{\"category\":\"rule\",\"author\":\"JTolkien\",\"name\":\"AllowAccess_JIT\",\"id\":\"0-395-19395-8\",\"priority\":22.99}],\"Reviewer\":{\"UserName\":\"jdow\",\"Role\":\"Admin\"}}}"
| alter all_authors_deep_scan = json_path_extract(json_field, "$..author")
| fields event_id, all_authors_deep_scan
```

**Explanation**: The query uses the `$..author` syntax to perform a deep scan of the entire JSON object, locating and extracting the value of every key named `author`, regardless of its nesting level.

**Output**:

| EVENT\_ID | ALL\_AUTHORS\_DEEP\_SCAN                   |
| --------- | ------------------------------------------ |
| 101       | \["NRees","EWaugh","HMelville","JTolkien"] |

### Example 4: Extracting all direct properties of an object

**Goal**: Extract all direct properties (keys and values) of a specific object using the wildcard operator.

**XQL code**:

```sql
config timeframe = 1d
| dataset = sample_xql_raw
| limit 1
| alter json_field = "{\"Firewall\":{\"ServerAccessConfig\":[{\"category\":\"policy\",\"author\":\"NRees\",\"name\":\"CustomerSuccess_NoAccess\",\"priority\":8.95},{\"category\":\"rule\",\"author\":\"EWaugh\",\"name\":\"AllowAccess_10_10_10_10\",\"id\":\"0-553-21311-3\",\"priority\":12.99},{\"category\":\"policy\",\"author\":\"HMelville\",\"name\":\"SOC_Access\",\"priority\":8.99},{\"category\":\"rule\",\"author\":\"JTolkien\",\"name\":\"AllowAccess_JIT\",\"id\":\"0-395-19395-8\",\"priority\":22.99}],\"Reviewer\":{\"UserName\":\"jdow\",\"Role\":\"Admin\"}}}"
| alter firewall_properties = json_path_extract(json_field, "$.Firewall.*")
| fields event_id, firewall_properties
```

**Explanation**: The query uses `$.Firewall.*` to retrieve all child elements (both the `ServerAccessConfig` array and the `Reviewer` object) directly under the `Firewall` key.

**Output**:

| EVENT\_ID | FIREWALL\_PROPERTIES                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| --------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 101       | \[\[{"category": "policy","author": "NRees","name": "CustomerSuccess\_NoAccess","priority": 8.95},{"category": "rule","author": "EWaugh","name": "AllowAccess\_10\_10\_10\_10","id": "0-553-21311-3","priority": 12.99},{"category": "policy","author": "HMelville","name": "SOC\_Access","priority": 8.99},{"category": "rule","author": "JTolkien","name": "AllowAccess\_JIT","id": "0-395-19395-8","priority": 22.99}],{"UserName": "jdow","Role": "Admin"}] |

### Example 5: Deep scan for specific keys

**Goal**: Use recursive descent to find all values associated with a specific key anywhere in the JSON.

**XQL code**:

```sql
config timeframe = 1d
| dataset = sample_xql_raw
| limit 1
| alter json_field = "{\"Firewall\":{\"ServerAccessConfig\":[{\"category\":\"policy\",\"author\":\"NRees\",\"name\":\"CustomerSuccess_NoAccess\",\"priority\":8.95},{\"category\":\"rule\",\"author\":\"EWaugh\",\"name\":\"AllowAccess_10_10_10_10\",\"id\":\"0-553-21311-3\",\"priority\":12.99},{\"category\":\"policy\",\"author\":\"HMelville\",\"name\":\"SOC_Access\",\"priority\":8.99},{\"category\":\"rule\",\"author\":\"JTolkien\",\"name\":\"AllowAccess_JIT\",\"id\":\"0-395-19395-8\",\"priority\":22.99}],\"Reviewer\":{\"UserName\":\"jdow\",\"Role\":\"Admin\"}}}"
| alter all_priorities = json_path_extract(json_field, "$.Firewall..priority")
| fields event_id, all_priorities
```

**Explanation**: The query uses `$.Firewall..priority` to recursively scan within the `Firewall` object and extract every value associated with the key `priority`.

**Output**:

| EVENT\_ID | ALL\_PRIORITIES          |
| --------- | ------------------------ |
| 101       | \[8.95,12.99,8.99,22.99] |

### Example 6: Extracting the last element of an array (length calculation)

**Goal**: Extract the last element of an array using the length property in a script expression.

**XQL code**:

```sql
config timeframe = 1d
| dataset = sample_xql_raw
| limit 1
| alter json_field = "{\"Firewall\":{\"ServerAccessConfig\":[{\"category\":\"policy\",\"author\":\"NRees\",\"name\":\"CustomerSuccess_NoAccess\",\"priority\":8.95},{\"category\":\"rule\",\"author\":\"EWaugh\",\"name\":\"AllowAccess_10_10_10_10\",\"id\":\"0-553-21311-3\",\"priority\":12.99},{\"category\":\"policy\",\"author\":\"HMelville\",\"name\":\"SOC_Access\",\"priority\":8.99},{\"category\":\"rule\",\"author\":\"JTolkien\",\"name\":\"AllowAccess_JIT\",\"id\":\"0-395-19395-8\",\"priority\":22.99}],\"Reviewer\":{\"UserName\":\"jdow\",\"Role\":\"Admin\"}}}"
| alter last_access_config_item = json_path_extract(json_field, "$..ServerAccessConfig[(@.length-1)]")
| fields event_id, last_access_config_item
```

**Explanation**: The query uses the script expression `(@.length-1)` to calculate the index of the last element in the `ServerAccessConfig` array and extract it.

**Output**:

| EVENT\_ID | LAST\_ACCESS\_CONFIG\_ITEM                                                                                      |
| --------- | --------------------------------------------------------------------------------------------------------------- |
| 101       | \[{"category": "rule","author": "JTolkien","name": "AllowAccess\_JIT","id": "0-395-19395-8","priority": 22.99}] |

### Example 7: Extracting the last element of an array (slice notation)

**Goal**: Extract the last element of an array using Python-style slice notation.

**XQL code**:

```sql
config timeframe = 1d
| dataset = sample_xql_raw
| limit 1
| alter json_field = "{\"Firewall\":{\"ServerAccessConfig\":[{\"category\":\"policy\",\"author\":\"NRees\",\"name\":\"CustomerSuccess_NoAccess\",\"priority\":8.95},{\"category\":\"rule\",\"author\":\"EWaugh\",\"name\":\"AllowAccess_10_10_10_10\",\"id\":\"0-553-21311-3\",\"priority\":12.99},{\"category\":\"policy\",\"author\":\"HMelville\",\"name\":\"SOC_Access\",\"priority\":8.99},{\"category\":\"rule\",\"author\":\"JTolkien\",\"name\":\"AllowAccess_JIT\",\"id\":\"0-395-19395-8\",\"priority\":22.99}],\"Reviewer\":{\"UserName\":\"jdow\",\"Role\":\"Admin\"}}}"
| alter last_access_config_slice = json_path_extract(json_field, "$..ServerAccessConfig[-1:]")
| fields event_id, last_access_config_slice
```

**Explanation**: The query uses the slice notation `[-1:]` to grab the last item in the `ServerAccessConfig` array.

**Output**:

| EVENT\_ID | LAST\_ACCESS\_CONFIG\_SLICE                                                                                     |
| --------- | --------------------------------------------------------------------------------------------------------------- |
| 101       | \[{"category": "rule","author": "JTolkien","name": "AllowAccess\_JIT","id": "0-395-19395-8","priority": 22.99}] |

### Example 8: Extracting elements by specific indices

**Goal**: Extract multiple elements from an array by specifying their indices.

**XQL code**:

```sql
config timeframe = 1d
| dataset = sample_xql_raw
| limit 1
| alter json_field = "{\"Firewall\":{\"ServerAccessConfig\":[{\"category\":\"policy\",\"author\":\"NRees\",\"name\":\"CustomerSuccess_NoAccess\",\"priority\":8.95},{\"category\":\"rule\",\"author\":\"EWaugh\",\"name\":\"AllowAccess_10_10_10_10\",\"id\":\"0-553-21311-3\",\"priority\":12.99},{\"category\":\"policy\",\"author\":\"HMelville\",\"name\":\"SOC_Access\",\"priority\":8.99},{\"category\":\"rule\",\"author\":\"JTolkien\",\"name\":\"AllowAccess_JIT\",\"id\":\"0-395-19395-8\",\"priority\":22.99}],\"Reviewer\":{\"UserName\":\"jdow\",\"Role\":\"Admin\"}}}"
| alter first_two_configs_by_index = json_path_extract(json_field, "$..ServerAccessConfig[0,1]")
| fields event_id, first_two_configs_by_index
```

**Explanation**: The query targets the `ServerAccessConfig` array and uses `[0,1]` to extract only the first and second elements.

**Output**:

| EVENT\_ID | FIRST\_TWO\_CONFIGS\_BY\_INDEX                                                                                                                                                                                         |
| --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 101       | \[{"category": "policy","author": "NRees","name": "CustomerSuccess\_NoAccess","priority": 8.95},{"category": "rule","author": "EWaugh","name": "AllowAccess\_10\_10\_10\_10","id": "0-553-21311-3","priority": 12.99}] |

### Example 9: Extracting a slice from the beginning of an array

**Goal**: Extract a range of elements from the start of an array using slice notation.

**XQL code**:

```sql
config timeframe = 1d
| dataset = sample_xql_raw
| limit 1
| alter json_field = "{\"Firewall\":{\"ServerAccessConfig\":[{\"category\":\"policy\",\"author\":\"NRees\",\"name\":\"CustomerSuccess_NoAccess\",\"priority\":8.95},{\"category\":\"rule\",\"author\":\"EWaugh\",\"name\":\"AllowAccess_10_10_10_10\",\"id\":\"0-553-21311-3\",\"priority\":12.99},{\"category\":\"policy\",\"author\":\"HMelville\",\"name\":\"SOC_Access\",\"priority\":8.99},{\"category\":\"rule\",\"author\":\"JTolkien\",\"name\":\"AllowAccess_JIT\",\"id\":\"0-395-19395-8\",\"priority\":22.99}],\"Reviewer\":{\"UserName\":\"jdow\",\"Role\":\"Admin\"}}}"
| alter slice_from_beginning = json_path_extract(json_field, "$..ServerAccessConfig[:2]")
| fields event_id, slice_from_beginning
```

**Explanation**: The query uses `[:2]` to extract a slice of the `ServerAccessConfig` array starting from the beginning up to (but not including) index 2.

**Output**:

| EVENT\_ID | SLICE\_FROM\_BEGINNING                                                                                                                                                                                                 |
| --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 101       | \[{"category": "policy","author": "NRees","name": "CustomerSuccess\_NoAccess","priority": 8.95},{"category": "rule","author": "EWaugh","name": "AllowAccess\_10\_10\_10\_10","id": "0-553-21311-3","priority": 12.99}] |

### **Example 10: Extract data using different JSONPath expressions**

**Goal**: Define a JSON object called Firewall within a JSON field to illustrate multiple ways you can use the json\_path\_extract function to extract data using different JSONPath configurations.

**XQL Code**:

```sql
dataset = xdr_data | limit 1   
| alter  json_field = "{\"Firewall\": {\"ServerAccessConfig\": [{\"category\": \"policy\",\"author\": \"NRees\",\"name\": \"CustomerSuccess_NoAccess\",\"priority\": 8.95},{\"category\": \"rule\",\"author\": \"EWaugh\",\"name\": \"AllowAccess_10_10_10_10\",\"id\": \"0-553-21311-3\",\"priority\": 12.99},{\"category\": \"policy\",\"author\": \"HMelville\",\"name\": \"SOC_Access\",\"priority\": 8.99},{\"category\": \"rule\",\"author\": \"JTolkien\",\"name\": \"AllowAccess_JIT\",\"id\": \"0-395-19395-8\",\"priority\": 22.99}],\"Reviewer\": {\"UserName\": \"jdow\",\"Role\": \"Admin\"}}}"  
| alter a = json_path_extract(json_field, "$.Firewall.ServerAccessConfig[*].author")   
| alter b = json_path_extract(json_field, "$..*[?(@.priority==22.99)].author")   
| alter c = json_path_extract(json_field, "$..author")   
| alter d = json_path_extract(json_field, "$.Firewall.*")   
| alter e = json_path_extract(json_field, "$.Firewall..priority")   
| alter f = json_path_extract(json_field, "$..ServerAccessConfig[(@.length-1)]")   
| alter g = json_path_extract(json_field, "$..ServerAccessConfig[-1:]")   
| alter h = json_path_extract(json_field, "$..ServerAccessConfig[0,1]")   
| alter i = json_path_extract(json_field, "$..ServerAccessConfig[:2]")  
| fields json_field, a, b, c, d, e, f, g, h, i
```

**Explanation**: This query applies various JSONPath expressions to the json\_field to extract different sets of data. Here is how the different fields are configured:

* **a**: Outputs all of the values for the author key in the ServerAccessConfig JSON array.
* **b**: Outputs all of the values for the author key, where the value for the priority key is 22.99.
* **c**: Outputs all of the values for the author key anywhere found in the JSON.
* **d**: Outputs all of the values under the Firewall key.
* **e**: Outputs all of the values under the Firewall key for the priority key.
* **f**: Outputs the JSON array index value from the ServerAccessConfig JSON array according to its index location from the end of the array.
* **g**: Outputs all of the JSON array index values from the ServerAccessConfig JSON array according to its index location from the end of the array.
* **h**: Outputs a specific set of JSON array index values (one or more) from the ServerAccessConfig JSON array according to its index location.
* **i**: Outputs all of the JSON array index values from the ServerAccessConfig JSON array according to its index location from the start (0 Index) up to the mentioned index value.

**Output**:

| \_TIME                 | JSON\_FIELD                                                                                                                                                                                       | A                                  | B        | C                                  | D                                                                                                                                                                                   |
| ---------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------- | -------- | ---------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Jan 20th 2025 18:51:42 | {"Firewall": {"ServerAccessConfig": \[{"category": "policy", "author": "NRees", "name": "CustomerSuccess\_NoAccess", "priority": 8.95}, ...], "Reviewer": {"UserName": "jdow", "Role": "Admin"}}} | NRees, EWaugh, HMelville, JTolkien | JTolkien | NRees, EWaugh, HMelville, JTolkien | {"ServerAccessConfig": \[{"category": "policy", "author": "NRees", "name": "CustomerSuccess\_NoAccess", "priority": 8.95}, ...], "Reviewer": {"UserName": "jdow", "Role": "Admin"}} |

*Note: Columns E through I, \_PRODUCT, \_VENDOR, and INSERT\_TIMESTAMP are also returned by the query but omitted from this sample display for brevity.*

## Related articles

* **Stages**: [`alter`](/xql-command-reference-guide/readme/stages/alter.md)
* **Functions**: [`json_extract`](/xql-command-reference-guide/readme/functions/json_extract.md), [`json_extract_array`](/xql-command-reference-guide/readme/functions/json_extract_array.md), [`json_extract_scalar`](/xql-command-reference-guide/readme/functions/json_extract_scalar.md), [`json_extract_scalar_array`](/xql-command-reference-guide/readme/functions/json_extract_scalar_array.md), [`to_json_string`](/xql-command-reference-guide/readme/functions/to_json_string.md)


---

# 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, and the optional `goal` query parameter:

```
GET https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/json_path_extract.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

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.