For the complete documentation index, see llms.txt. This page is also available as Markdown.

json_extract_scalar

Use the json_extract_scalar() function to retrieve a single, atomic value—such as a string, number, or boolean—from a JSON object.

Syntax

json_extract_scalar (<json_object_formatted_string>, <json_path>)

Syntactic Sugar syntax

<json_object_formatted_string> -> <field_path>

Parameters

Name
Type
Required
Description

json_object_formatted_string

string

Yes

The string representation of the JSON object.

json_path

string

Yes

The path to the specific scalar value to extract, typically starting with $ to represent the root.

Returns

The json_extract_scalar() function returns the extracted value as a string. If the targeted JSON field is an object or an array, or if the path does not exist, the function returns NULL.

Usage notes

  • The input <json_object_formatted_string> must be a string representation of a JSON object. You may need to use to_json_string() to convert fields like nested_json_data before extraction.

  • This function always returns the extracted value as a string. To use the value as a number or boolean, you must chain functions like to_integer(), to_float(), or to_boolean().

  • JSON field names are case-sensitive. The key in your json_path must match the case in the JSON object exactly.

  • XQL supports a "syntactic sugar" format for this function: <json_object_formatted_string> -> <field_path>. In this format, the leading $ is not required.

  • When a field name within the <json_path> contains special characters like a dot (.) or colon (:):

    • In regular syntax, enclose the field in single quotes within brackets: json_extract_scalar(<json_object_formatted_string>, "['<json_field>']").

    • In Syntactic Sugar syntax, enclose the field in double quotes within brackets: <json_object_formatted_string> -> ["<json_field>"].

Examples

Example 1: Extracting a top-level string scalar value (status)

Goal: Extract a simple string value directly from the simple_json_data field using both regular syntax and syntactic sugar.

XQL code:

Explanation: This query converts simple_json_data to a JSON string and extracts the value associated with the "status" key.

Output:

event_id
simple_json_data
extracted_status

101

{"status": "ok", "code": 200}

"ok"

102

{"status": "fail", "error": "access_denied"}

"fail"

Example 2: Extracting a numeric scalar value (code) and converting to integer

Goal: Extract a numeric value (which returns as a string) and explicitly convert it to an integer.

XQL code:

Explanation: For event 101, the code "200" is extracted as a string and then converted to the integer 200. For event 102, the code field does not exist, so NULL is returned.

Output:

event_id
simple_json_data
extracted_code_str
extracted_code_int

101

{"status": "ok", "code": 200}

"200"

200

102

{"status": "fail", "error": "access_denied"}

NULL

NULL

Example 3: Extracting a nested string scalar value (user.name)

Goal: Traverse a nested JSON structure to extract a specific scalar value.

XQL code:

Explanation: For event 101, the query navigates to user.name and extracts "Alice". For event 102, the user object is not present, resulting in NULL.

Output:

event_id
nested_json_data
extracted_username

101

{"user": {"id": "U1", "name": "Alice"}, ...}

"Alice"

102

{"process": {"name": "cmd.exe", "pid": 1234}, ...}

NULL

Example 4: Handling special characters in nested key names

Goal: Extract a value where the key name contains a special character (a dot), requiring specific bracket notation.

XQL code:

Explanation: The key 'a.b' is enclosed in single quotes within brackets ['a.b'] because it contains a dot, adhering to XQL's regular JSON path rules.

Output:

event_id
test_json_data
extracted_special_char_regular

101

{"a.b": {"scalar_field": "Special Value"}}

"Special Value"

Example 5: Attempting to extract an object or array (returns NULL)

Goal: Demonstrate that the function returns NULL when the targeted path points to a complex structure (object or array) instead of a scalar.

XQL code:

Explanation: Attempting to extract the root $ of array_of_json_objects (which is an array) using json_extract_scalar results in NULL because it is designed only for scalar values.

Output:

event_id
array_of_json_objects
extracted_array_as_scalar

101

[{"action": "read", ...}]

NULL

102

[{"event": "file_open", ...}]

NULL

Example 6: Handling non-existent paths (returns NULL)

Goal: Demonstrate the behavior when the specified JSON path does not exist in the object.

XQL code:

Explanation: Because nonExistentField is not present in the JSON structure, the function gracefully returns NULL.

Output:

event_id
simple_json_data
extracted_non_existent

101

{"status": "ok", "code": 200}

NULL

102

{"status": "fail", "error": "access_denied"}

NULL

Example 7: Filtering using an XQL-native datatype

Goal: Return the storage_device_drive_type value from the action_file_device_info field, and return the record if it is 1.

XQL code:

Explanation: This query converts the action_file_device_info object to a JSON string using to_json_string(), and then uses json_extract_scalar() to extract the storage_device_drive_type value. Because the extraction returns a string, to_integer() is used to cast the value into an XQL-native integer datatype before the filter stage evaluates if the value equals 1.

Output:

AFDI
SDN

{"storage_device_drive_type": 1, "vendor": "SanDisk"}

1

{"storage_device_drive_type": 1, "vendor": "Samsung"}

1

Example 8: Filtering using a string

Goal: Return the storage_device_drive_type value from the action_file_device_info field, and return the record if it matches the string value of "1".

XQL code:

Explanation: Similar to the first example, this query extracts the storage_device_drive_type value as a string. However, instead of converting the datatype, the filter stage evaluates the result directly against the string literal "1".

Output:

AFDI
SDN

{"storage_device_drive_type": "1", "vendor": "SanDisk"}

"1"

{"storage_device_drive_type": "1", "vendor": "Samsung"}

"1"

Example 9: Filtering using Syntactic Sugar Format

Goal: Return the storage_device_drive_type value from the action_file_device_info field using the shorthand syntactic sugar format.

XQL code:

Explanation: This query achieves the exact same result as Example 7 but utilizes the JSON extract operator (->) as syntactic sugar. This shorthand format replaces the explicit json_extract_scalar() function call, making the query more concise while still converting the extracted string to an integer for the final filter evaluation.

Output:

AFDI
SDN

{"storage_device_drive_type": 1, "vendor": "SanDisk"}

1

{"storage_device_drive_type": 1, "vendor": "Samsung"}

1

Last updated

Was this helpful?