json_extract

Learn more about the Cortex Query Language json_extract() function that accepts a string representing a JSON object, and returns a field value from that object.

Important

Before using this JSON function, it's important that you understand how Cortex XDR treats a JSON in the Cortex Query Language. For more information, see JSON functions.

Syntax

Regular Syntax

json_extract(<json_object_formatted_string>, <json_path>)

When a field in the <json_path> contains characters, such as a dot (.) or colon (:), use the syntax:

json_extract(<json_object_formatted_string>, "['<json_field>']")

Syntactic Sugar Format

To make it easier for you to write your XQL queries, you can also use the following syntactic sugar format.

<json_object_formatted_string> -> <json_path>{}

When a field in the <json_path> contains characters, such as a dot (.) or colon (:), use the syntax:

<json_object_formatted_string> -> ["<json_field>"]{}

Description

The json_extract() function extracts inner JSON objects by retrieving the value from the identified field. The returned datatype is always a string. If the input string does not represent a JSON object, this function fails to parse. To convert a string field to a JSON object, use the to_json_string function.

Important

JSON field names are case sensitive, so the key to field pairing must be identical in an XQL query for results to be found. For example, if a field value is "TIMESTAMP" and your query is defined to look for "timestamp", no results will be found.

Note

The field value is always returned as a string. To return the scalar values, which are not an object or an array, use json_extract_scalar.

Examples

Return the storage_device_name value from the action_file_device_info field.

dataset = xdr_data 
| fields action_file_device_info as afdi 
| alter sdn = json_extract(to_json_string(afdi), "$.storage_device_name") 
| filter afdi != null

Using Syntactic Sugar Format

The same example above with a syntactic sugar format.

dataset = xdr_data
| fields action_file_device_info as afdi
| alter sdn = to_json_string(afdi)->storage_device_name{}
| filter afdi != null