json_extract ↗

Use the json_extract() function to extract a specific JSON object or array from within a larger JSON-formatted string by navigating a specified path.

Syntax

json_extract (<json_object_formatted_string>, <json_path>)

Syntactic Sugar Format

<json_object_formatted_string> -> <json_path>{}

Parameters

Name Type Required Description
json_object_formatted_string string Yes The field containing the JSON string from which to extract data. If the field is an object or array, use to_json_string() to convert it first.
json_path string Yes The path to the data within the JSON object. In regular syntax, it starts with $. In syntactic sugar, the $ is omitted. Use brackets ['key'] or ["key"] for keys with special characters.

Returns

The json_extract() function returns a string representation of the extracted JSON object or array.

Usage notes

  • Input requirement: The function requires a field that contains a JSON-formatted string. If your field is a native XQL object or array, you must convert it using to_json_string() before extraction.
  • Output format: The function always returns a string. If the path points to a scalar value (string, number, boolean), it returns that value quoted as a JSON literal string. For direct scalar extraction without quotes (for numbers/booleans), consider using json_extract_scalar().
  • Case sensitivity: JSON field names are case-sensitive. The key in your json_path must exactly match the case of the field name in the JSON object.
  • Handling special characters: When a field name in the path contains special characters like a dot (.) or colon (:):
    • Regular syntax: Use the syntax json_extract(<json_object_formatted_string>, "['<json_field>']").
    • Syntactic sugar syntax: Use the syntax <json_object_formatted_string> -> ["<json_field>"]{}.
  • Error handling: If the input string is not a valid JSON object, or if the specified json_path does not exist, the function returns NULL.

Examples

Example 1: Basic object property extraction

Goal: Extract a nested JSON object from a parent object.

XQL code:

Regular Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter extracted_user_info = json_extract(to_json_string(nested_json_data), "$.user") 
| fields event_id, nested_json_data, extracted_user_info 
| limit 1

Syntactic Sugar Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter extracted_user_info = to_json_string(nested_json_data) -> user{} 
| fields event_id, nested_json_data, extracted_user_info 
| limit 1

Explanation: For event_id 101, the nested_json_data field contains a user object. The query converts the field to a JSON string and extracts the entire nested object, including keys and values.

Output:

EVENT_ID NESTED_JSON_DATA EXTRACTED_USER_INFO
101 {"user": {"id": "U1", "name": "Alice"}, "session": {...}} {"id": "U1", "name": "Alice"}

Example 2: Extracting a deeper nested object property

Goal: Navigate deeper into a JSON structure to extract a specific nested object.

XQL code:

Regular Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter extracted_session_info = json_extract(to_json_string(nested_json_data), "$.session") 
| fields event_id, nested_json_data, extracted_session_info 
| limit 1

Syntactic Sugar Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter extracted_session_info = to_json_string(nested_json_data) -> session{} 
| fields event_id, nested_json_data, extracted_session_info 
| limit 1

Explanation: The query extracts the session object, which is nested within nested_json_data, holding the session details.

Output:

EVENT_ID NESTED_JSON_DATA EXTRACTED_SESSION_INFO
101 {"user": {...}, "session": {"start": "10:00", "type": "web"}} {"start": "10:00", "type": "web"}

Example 3: Extracting a JSON object from an array using index

Goal: Extract a specific JSON object from an array field using its index.

XQL code:

Regular Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter extracted_first_action_obj = json_extract(to_json_string(array_of_json_objects), "$[0]") 
| fields event_id, array_of_json_objects, extracted_first_action_obj 
| limit 1

Syntactic Sugar Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter extracted_first_action_obj = to_json_string(array_of_json_objects) -> [0]{} 
| fields event_id, array_of_json_objects, extracted_first_action_obj 
| limit 1

Explanation: The field array_of_json_objects contains an array of objects. The path $[0] (or [0]{}) extracts the first object in the array as a string.

Output:

EVENT_ID ARRAY_OF_JSON_OBJECTS EXTRACTED_FIRST_ACTION_OBJ
101 [{"action": "read", "file": "doc1.txt"}, {"action": "write", ...}] {"action": "read", "file": "doc1.txt"}

Example 4: Extracting the entire JSON field content (root extraction)

Goal: Extract the complete content of a JSON-formatted field as a string.

XQL code:

Regular Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter extracted_full_json = json_extract(to_json_string(simple_json_data), "$") 
| fields event_id, simple_json_data, extracted_full_json 
| limit 1

Syntactic Sugar Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter extracted_full_json = to_json_string(simple_json_data) -> {} 
| fields event_id, simple_json_data, extracted_full_json 
| limit 1

Explanation: Using $ or {} with an empty string as the path signifies that the entire JSON content is to be extracted and returned.

Output:

EVENT_ID SIMPLE_JSON_DATA EXTRACTED_FULL_JSON
101 {"status": "ok", "code": 200} {"status": "ok", "code": 200}

Example 5: Extraction with a key containing special characters

Goal: Extract data when a key name contains special characters like a dot (.).

XQL code:

Regular Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| limit 1 
| alter json_with_special_key = to_string("{\"device.info\": {\"model\": \"XDR Agent\", \"version\": \"8.0\"}}") 
| alter extracted_special_key_obj = json_extract(json_with_special_key, "$['device.info']") 
| fields event_id, json_with_special_key, extracted_special_key_obj

Syntactic Sugar Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| limit 1 
| alter json_with_special_key = to_string("{\"device.info\": {\"model\": \"XDR Agent\", \"version\": \"8.0\"}}") 
| alter extracted_special_key_obj = json_with_special_key -> ["device.info"]{} 
| fields event_id, json_with_special_key, extracted_special_key_obj

Explanation: The key device.info contains a dot. In regular syntax, it is enclosed in single quotes and brackets ['device.info']. In syntactic sugar, it uses double quotes and brackets ["device.info"].

Output:

EVENT_ID JSON_WITH_SPECIAL_KEY EXTRACTED_SPECIAL_KEY_OBJ
101 {"device.info": {"model": "XDR Agent", "version": "8.0"}} {"model": "XDR Agent", "version": "8.0"}

Example 6: Handling a path that does not exist

Goal: Demonstrate behavior when the specified JSON path is not found.

XQL code:

Regular Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 103 
| alter extracted_non_existent = json_extract(to_json_string(simple_json_data), "$.status") 
| fields event_id, simple_json_data, extracted_non_existent 
| limit 1

Syntactic Sugar Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 103 
| alter extracted_non_existent = to_json_string(simple_json_data) -> status{} 
| fields event_id, simple_json_data, extracted_non_existent 
| limit 1

Explanation: For event_id 103, the simple_json_data field does not contain a status key. The function returns NULL.

Output:

EVENT_ID SIMPLE_JSON_DATA EXTRACTED_NON_EXISTENT
103 {"connection_id": "CONN-001", "protocol": "TCP"} NULL

Example 7: Handling non-JSON formatted string input

Goal: Demonstrate behavior when the input is not valid JSON.

XQL code:

Regular Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter non_json_input = event_description 
| alter result_from_non_json = json_extract(non_json_input, "$") 
| fields event_id, non_json_input, result_from_non_json 
| limit 1

Syntactic Sugar Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter non_json_input = event_description 
| alter result_from_non_json = non_json_input -> {} 
| fields event_id, non_json_input, result_from_non_json 
| limit 1

Explanation: The event_description field is a plain string ("User login successful"), not a JSON structure. The function fails to parse it and returns NULL.

Output:

EVENT_ID NON_JSON_INPUT RESULT_FROM_NON_JSON
101 "User login successful" NULL

Example 8: Using to_json_string() for explicit type conversion

Goal: Ensure input is a valid JSON string by explicitly converting a native object field.

XQL code:

Regular Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter nested_data_as_string = to_json_string(nested_json_data) 
| alter extracted_user_id = json_extract(nested_data_as_string, "$.user.id") 
| fields event_id, nested_json_data, nested_data_as_string, extracted_user_id 
| limit 1

Syntactic Sugar Syntax

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 101 
| alter nested_data_as_string = to_json_string(nested_json_data) 
| alter extracted_user_id = nested_data_as_string -> user.id{} 
| fields event_id, nested_json_data, nested_data_as_string, extracted_user_id 
| limit 1

Explanation: The nested_json_data field is an object. to_json_string() is used to convert it into a valid JSON string required by json_extract(). The value "U1" is extracted as the string "U1".

Output:

EVENT_ID NESTED_JSON_DATA NESTED_DATA_AS_STRING EXTRACTED_USER_ID
101 {"user": {"id": "U1", "name": "Alice"}, ...} {"user": {"id": "U1", "name": "Alice"}, ...} (as string) ""U1""