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
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 useto_json_string()to convert fields likenested_json_databefore 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(), orto_boolean().JSON field names are case-sensitive. The key in your
json_pathmust 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:
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:
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:
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:
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:
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:
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:
{"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:
{"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:
{"storage_device_drive_type": 1, "vendor": "SanDisk"}
1
{"storage_device_drive_type": 1, "vendor": "Samsung"}
1
Related articles
Functions:
to_json_string,to_integer,to_float,to_boolean
Last updated
Was this helpful?
