XQL JSON Functions Reference ↗

Core Concept: Why XQL has four JSON extraction functions

Unlike dynamic JSON parsing in other query languages, XQL requires you to specify the expected data type at the time of extraction. You must choose the correct function based on what you are extracting:

• json_extract() — Use when extracting a JSON object or when you need to preserve the full JSON structure of the value.
• json_extract_scalar() — Use when extracting a primitive value (string, number, or boolean) and you need a clean, unquoted string — especially for filter comparisons.
• json_extract_array() — Use when extracting a JSON array, including arrays of objects.
• json_extract_scalar_array() — Use when extracting a JSON array of primitive values and you need clean, unquoted elements for comparisons.

---

json_extract()

The json_extract() function navigates and retrieves a specific value from a nested JSON object. The function drills down into a JSON string to surface a single piece of data as a new field. The json_extract() function always returns a string. If the extracted value is a string in the JSON, the result includes the literal double-quote characters (e.g., "\"Medium\""). This is different from json_extract_scalar(), which strips the quotes.

Constraint: The input field must be a valid JSON-formatted string. If the string is malformed or not in JSON format, the json_extract() function will return null. To convert a raw string field into JSON before extraction, use the to_json_string() function.

Syntax

Regular Syntax:

json_extract(field_name, "$.<json_path>")

Special characters syntax:

When a field in the JSON path contains reserved characters such as an at sign (@), dot (.), or colon (:), you must use bracket notation:

json_extract(field_name, "$[<json_path>]")

Parameters

Name	Type	Required	Description
field_name	string	Yes	The source field or expression containing a valid JSON-formatted string.
json_path	string	Yes	A JSONPath expression specifying the location of the value to extract (e.g., "$.key", "$.nested.key", "$.array[0].field").

Return Value

The json_extract() function always returns a string:

• String values → The json_extract() function returns the value with surrounding double quotes as literal characters (e.g., "\"Medium\"")
• JSON object → The json_extract() function returns a JSON-formatted string (e.g., {"name":"John","age":30})
• JSON array → The json_extract() function returns a JSON array string (e.g., ["a","b","c"])
• Number → The json_extract() function returns a string representation (e.g., "42")
• Boolean → The json_extract() function returns a string representation (e.g., "true")

The json_extract() function returns null if:

• The path does not exist.
• The input is not valid JSON.
• The value is JSON null.

Examples

Example 1: Extract a nested user object

dataset = xdr_data
| alter user_details = json_extract(action_evtlog_data_fields, "$.user")

Input:

action_evtlog_data_fields
{"user":{"name":"admin","role":"superuser"},"status":"success"}
{"user":{"name":"jdoe","role":"viewer"},"status":"failed"}

Output:

action_evtlog_data_fields	user_details
{"user":{"name":"admin","role":"superuser"},"status":"success"}	{"name":"admin","role":"superuser"}
{"user":{"name":"jdoe","role":"viewer"},"status":"failed"}	{"name":"jdoe","role":"viewer"}

Example 2: Extract a value from a key containing @

dataset = xdr_data
| alter email_from = json_extract(email_headers, "$[@from]")

Input:

email_headers
{"@from":"admin@company.com","@to":"user@company.com","subject":"Alert"}

Output:

email_headers	email_from
{"@from":"admin@company.com","@to":"user@company.com","subject":"Alert"}	"\"admin@company.com\""

Syntactic Sugar alternative

For a more concise query, XQL supports a Syntactic Sugar format using the -> operator with {} suffix. This compiles to the exact same json_extract() engine but improves human readability for simple paths.

Syntactic Sugar format:

field_name -> <json_path>{}

Special characters Syntactic Sugar format:

field_name -> ["<json_path>"]{}

Syntactic Sugar Example:

dataset = xdr_data
| alter user_details = action_evtlog_data_fields -> user{}

Notes

• Use json_extract() when you need to preserve the JSON type of the extracted value, especially when the result is a JSON object or array that you plan to further process.
• For scalar values (strings, numbers, booleans), prefer json_extract_scalar() for cleaner output without JSON quoting.
• Do not use json_extract() when the result will be used in a filter comparison — the literal JSON quotes will break the comparison. Use json_extract_scalar() instead.

---

json_extract_scalar(): How to Extract Plain Strings, Numbers, and Booleans

The json_extract_scalar() function retrieves a specific scalar value (string, number, or boolean) from a JSON string and returns it as a clean, unquoted string.

Unlike dynamic JSON parsing in other languages, XQL requires you to use json_extract_scalar() specifically when you need values stripped of literal JSON quotes, making it the required choice for filter comparisons or type-casting operations.

Constraint: The input field must be a valid JSON-formatted string. If the string is malformed, not in JSON format, or if the target path points to a complex object or array, the json_extract_scalar() function will return null. To convert a raw string field into JSON before extraction, use the to_json_string() function.

Canonical Syntax

Regular Syntax:

json_extract_scalar(field_name, "$.<json_path>")

Special characters syntax:

When a field in the JSON path contains reserved characters such as an at sign (@), dot (.), or colon (:), you must use bracket notation:

json_extract_scalar(field_name, "$[<json_path>]")

Parameters

Name	Type	Required	Description
field_name	string	Yes	The source field or expression containing a valid JSON-formatted string.
json_path	string	Yes	A JSONPath expression pointing to a scalar value (string, number, or boolean).

Return Value

The json_extract_scalar() function returns the requested scalar value as a plain string with literal JSON quotes stripped:

• String → The json_extract_scalar() function returns the value without surrounding quotes (e.g., "MacOSX" instead of "\"MacOSX\"")
• Number → The json_extract_scalar() function returns the value as its string representation (e.g., "443")
• Boolean → The json_extract_scalar() function returns the value as its string representation (e.g., "true")

The json_extract_scalar() function returns null if:

• The path does not exist.
• The value at the target path is a JSON object or a JSON array (non-scalar).
• The input field is not valid JSON.

Examples

Example 1: Extracting multiple scalar fields for a filter operation

Explanation: Because we intend to use the extracted event_type in a filter statement (filter event_type = "login"), we must use json_extract_scalar() instead of json_extract(). The json_extract_scalar() function strips the literal JSON quotes, preventing quote-matching errors during the boolean comparison. If json_extract() were used here, the value would be "\"login\"" and the filter event_type = "login" would never match.

dataset = xdr_data
| alter
    username   = json_extract_scalar(action_evtlog_data_fields, "$.user.name"),
    event_type = json_extract_scalar(action_evtlog_data_fields, "$.event.type"),
    source_ip  = json_extract_scalar(action_evtlog_data_fields, "$.event.src")
| filter event_type = "login"

Input:

action_evtlog_data_fields
{"user":{"name":"admin"},"event":{"type":"login","src":"10.0.0.1"}}
{"user":{"name":"jdoe"},"event":{"type":"login","src":"192.168.1.5"}}
{"user":{"name":"svc_acct"},"event":{"type":"logout","src":"10.0.0.2"}}

Output (after filter):

username	event_type	source_ip
admin	login	10.0.0.1
jdoe	login	192.168.1.5

Example 2: Extracting a value from a key containing special characters

Explanation: The target JSON key @from contains an at-sign. Therefore, the json_extract_scalar() function must use bracket notation ($[@from]) to successfully retrieve the clean string.

dataset = xdr_data
| alter sender = json_extract_scalar(email_headers, "$[@from]")

Input:

email_headers
{"@from":"admin@company.com","@to":"user@company.com","subject":"Alert"}

Output:

email_headers	sender
{"@from":"admin@company.com","@to":"user@company.com","subject":"Alert"}	admin@company.com

Note: Unlike json_extract(), the output does NOT include surrounding double quotes. The value is returned as a clean string, ready for direct comparisons and filtering.

Syntactic Sugar alternative

For a more concise query, XQL supports a Syntactic Sugar format using the -> operator. This compiles to the exact same json_extract_scalar() engine but improves human readability for simple paths.

Syntactic Sugar format:

field_name -> <json_path>

Special characters Syntactic Sugar format:

field_name -> ["<json_path>"]

Syntactic Sugar Example:

dataset = xdr_data
| alter
    username   = action_evtlog_data_fields -> user.name,
    event_type = action_evtlog_data_fields -> event.type
| filter event_type = "login"

Notes

• Unlike json_extract(), the json_extract_scalar() function strips JSON string quotes from the result, making it ideal for string comparisons and human-readable output.
• If the target value is an object or array, the json_extract_scalar() function returns null. Use json_extract() for those cases.
• Numeric and boolean values are returned as their string representations (e.g., 443, true). Use to_integer(), to_float(), or to_boolean() for type casting.
• If the initial field is not already a JSON object, it must be converted using to_json_string() before extraction can occur.

---

json_extract_array()

The json_extract_array() function converts a JSON-formatted string into a native XQL array. Use this function to transform stringified lists into actionable data that can be indexed or filtered within your queries.

Constraint: The input field must be a valid JSON-formatted string. If the string is malformed or not in JSON format, the json_extract_array() function will return null. To convert a raw string field into JSON before extraction, use the to_json_string() function.

Canonical Syntax

Regular Syntax:

json_extract_array(field_name, "$.<json_path>")

Special characters syntax:

When a field in the JSON path contains reserved characters such as an at sign (@), dot (.), or colon (:), you must use bracket notation:

json_extract_array(field_name, "$[<json_path>]")

Parameters

Name	Type	Required	Description
field_name	string	Yes	The source field or expression containing a valid JSON-formatted string.
json_path	string	Yes	A JSONPath expression pointing to a JSON array.

Return Value

The json_extract_array() function returns the extracted array as a native XQL array.

• Full Array ([]): The json_extract_array() function returns an XQL-native array (e.g., ["22", "443"]).
• Specific Element ([n]): The json_extract_array() function extracts the value at that index and returns it as a string (e.g., "22").

Important: Each element in the returned array retains surrounding double quotes when the array contains simple scalars. For example, string values are returned as "\"SSH\"" rather than SSH. Use json_extract_scalar_array() for clean unquoted values.

The json_extract_array() function returns null if:

• The path does not exist.
• The value at the path is not a JSON array.
• The input is not valid JSON.

Examples

Example 1: Extract an array of objects and expand into rows

dataset = xdr_data
| alter users = json_extract_array(response_json, "$.users")
| arrayexpand users
| alter
    user_name = json_extract_scalar(users, "$.name"),
    user_role = json_extract_scalar(users, "$.role")

Input:

response_json
{"users":[{"name":"admin","role":"super"},{"name":"jdoe","role":"viewer"},{"name":"asmith","role":"editor"}]}

Output (after arrayexpand):

users	user_name	user_role
{"name":"admin","role":"super"}	admin	super
{"name":"jdoe","role":"viewer"}	jdoe	viewer
{"name":"asmith","role":"editor"}	asmith	editor

Example 2: Extract an array from a key containing @

dataset = xdr_data
| alter recipients = json_extract_array(email_data, "$[@recipients]")

Input:

email_data
{"@recipients":["user1@company.com","user2@company.com","user3@company.com"],"subject":"Alert"}

Output:

email_data	recipients
{"@recipients":["user1@company.com","user2@company.com","user3@company.com"],"subject":"Alert"}	["\"user1@company.com\"","\"user2@company.com\"","\"user3@company.com\""]

Note: The output elements are doubly-quoted ("\"user1@company.com\"" instead of user1@company.com). For clean unquoted values, use json_extract_scalar_array() instead.

Syntactic Sugar alternative

For a more concise query, XQL supports a Syntactic Sugar format using the -> operator with [] suffix. This compiles to the exact same json_extract_array() engine but improves human readability for simple paths.

Syntactic Sugar format:

field_name -> <json_path>[]

Special characters Syntactic Sugar format:

field_name -> ["<json_path>"][]

Syntactic Sugar Example:

dataset = xdr_data
| alter users = response_json -> users[]
| arrayexpand users
| alter
    user_name = json_extract_scalar(users, "$.name"),
    user_role = json_extract_scalar(users, "$.role")

Notes

• The returned value is a JSON array. Use XQL array functions such as array_length(), array_contains(), or arrayexpand to work with the result.
• If the path points to a scalar or object (not an array), the json_extract_array() function returns null.
• Combine with arrayexpand to flatten array elements into individual rows, then use json_extract_scalar() to extract fields from each element.
• If the source data is a raw string, it must first be processed by to_json_string() before the array can be extracted.

---

json_extract_scalar_array()

The json_extract_scalar_array() function parses a string representing a JSON array and converts it into a native XQL array format. While functionally similar to json_extract_array(), this version extracts the elements as scalar values. This means the resulting array elements are returned in their raw form rather than being wrapped in JSON-style double quotes ("...").

Constraint: The input field must be a valid JSON-formatted string. If the string is malformed or not in JSON format, the json_extract_scalar_array() function will return null. To convert a raw string field into JSON before extraction, use the to_json_string() function.

Note: This function does not support Syntactic Sugar format. The function must always be called using the full regular function syntax.

Canonical Syntax

Regular Syntax:

json_extract_scalar_array(field_name, "$.<json_path>")

Special characters syntax:

When a field in the JSON path contains reserved characters such as an at sign (@), dot (.), or colon (:), you must use bracket notation:

json_extract_scalar_array(field_name, "$[<json_path>]")

Parameters

Name	Type	Required	Description
field_name	string	Yes	The source field or expression containing a valid JSON-formatted string.
json_path	string	Yes	A JSONPath expression pointing to a JSON array of scalar values (strings, numbers, or booleans).

Return Value

The json_extract_scalar_array() function returns an array of plain strings with JSON quotes stripped from each element:

• ["SSH", "HTTPS", "SNMP", "FTP"] → The json_extract_scalar_array() function returns SSH, HTTPS, SNMP, FTP
• [443, 80, 8080] → The json_extract_scalar_array() function returns 443, 80, 8080
• [true, false, true] → The json_extract_scalar_array() function returns true, false, true

The json_extract_scalar_array() function returns null if:

• The path does not exist.
• The value at the path is not a JSON array.
• The input is not valid JSON.

Examples

Example 1: Extract a scalar array and filter by value

Note: This function does not support Syntactic Sugar format. Use the regular function syntax shown below.

dataset = xdr_data
| alter blocked_ips = json_extract_scalar_array(network_json, "$.blocked_ips")
| filter array_any(blocked_ips, "10.0.0.5")

Input:

network_json
{"blocked_ips":["10.0.0.5","192.168.1.1","172.16.0.10"]}
{"blocked_ips":["10.0.0.5","10.10.10.1"]}
{"blocked_ips":["192.168.1.1","172.16.0.10"]}

Output (after filter):

network_json	blocked_ips
{"blocked_ips":["10.0.0.5","192.168.1.1","172.16.0.10"]}	10.0.0.5, 192.168.1.1, 172.16.0.10
{"blocked_ips":["10.0.0.5","10.10.10.1"]}	10.0.0.5, 10.10.10.1

Note: Because the values are clean strings (not doubly-quoted), array_contains(blocked_ips, "10.0.0.5") works directly without any trim() or replex() workarounds.

Example 2: Extract an array from a key containing @

dataset = xdr_data
| alter recipients = json_extract_scalar_array(email_data, "$[@recipients]")

Input:

email_data
{"@recipients":["user1@company.com","user2@company.com","user3@company.com"],"subject":"Alert"}

Output:

email_data	recipients
{"@recipients":["user1@company.com","user2@company.com","user3@company.com"],"subject":"Alert"}	user1@company.com, user2@company.com, user3@company.com

Note: Compare this with json_extract_array() which would return ["\"user1@company.com\"","\"user2@company.com\"","\"user3@company.com\""]. The json_extract_scalar_array() function returns clean, unquoted values.

Notes

• The json_extract_scalar_array() function is the array equivalent of json_extract_scalar(): it strips JSON string quotes from each element in the array.
• Best used when the JSON array contains only scalar values (strings, numbers, booleans). If the array contains objects or nested arrays, use json_extract_array() instead.
• The result is compatible with XQL array functions: array_contains(), array_length(), arrayexpand, etc.
• The json_extract_scalar_array() function does not support Syntactic Sugar format — it must always be called using the full function syntax.

---

Comparison Table

Function	Returns	Strips JSON Quotes	Syntactic Sugar format	Best For
json_extract()	String (always)	No	field -> path{}	Extracting any value while preserving JSON formatting
json_extract_scalar()	Plain string (scalar only)	Yes	field -> path	Extracting a single string/number/boolean for filters
json_extract_array()	XQL array (elements doubly-quoted)	No	field -> path[]	Extracting arrays (including arrays of objects)
json_extract_scalar_array()	XQL array of plain strings	Yes	No Not supported	Extracting arrays of scalar values for comparisons

---

Syntactic Sugar format (->) vs Regular Syntax — Performance and Behavior

Aspect	Syntactic Sugar format (-> operator)	Regular Syntax (json_extract*)	Performance Difference
Underlying Engine	Compiled to the same internal JSON extraction engine	Same internal JSON extraction engine	🟢 No difference
Return Type	Returns value directly (like json_extract_scalar for leaves)	json_extract_scalar returns clean string; json_extract has quotes	🟢 No difference
Nested Access	field -> nested.path.value	json_extract_scalar(field, "$.nested.path.value")	🟢 No difference
Array Access	field -> array[0]	arrayindex(json_extract_array(field, "$.array"), 0)	🟢 No difference
Quote Handling	Returns clean values — no surrounding quotes	json_extract_scalar: clean; json_extract: has quotes	🟡 Sugar avoids quotes
Readability	High — concise, natural dot notation	Lower — verbose function calls with JSONPath strings	N/A
Flexibility	Limited — cannot use complex JSONPath (special chars, wildcards)	Full JSONPath support including bracket notation, wildcards	N/A
Use in arraymap	Cannot use -> on @element	json_extract_scalar("@element", "$.field") works	N/A
Chaining	field -> level1.level2.level3 (unlimited depth)	Requires nested calls or complex JSONPath strings	🟢 No difference
Error Behavior	Returns null for missing paths	Returns null for missing paths	🟢 No difference
Filter Statements	Cannot use -> in filter expressions reliably	filter json_extract_scalar(field, "$.type") = "value" works	N/A

---

When to use which syntax — Decision Guide

Scenario	Recommended Syntax	Reason	Example
Simple leaf value from a known field	Syntactic Sugar format (->)	Cleaner, no quote issues, same performance	event_data -> ProcessId
Key with @ or special characters	json_extract_scalar()	Syntactic Sugar format cannot handle special chars in keys	json_extract_scalar(headers, "$[@from]")
Array index access (e.g., first element)	json_extract_array() + arrayindex()	Syntactic Sugar format has limited array index support	arrayindex(json_extract_array(target, "$"), 0)
Iterating over array elements with arraymap	json_extract_scalar() on @element	Syntactic Sugar format cannot be used on @element	arraymap(Entities, json_extract_scalar("@element", "$.hostName"))
Extracting a sub-object for further processing	json_extract()	Need to preserve JSON structure	json_extract(winlog, "$.event_data")
Array of simple scalar values	json_extract_scalar_array()	Returns clean strings without quotes	json_extract_scalar_array(user, "$.rolegrants")
Array of complex objects to iterate	json_extract_array()	Returns array for arraymap/arrayfilter	json_extract_array(properties, "$.entities")
Filter/conditional expressions	json_extract_scalar()	More reliable in filter statements; no quote issues	filter json_extract_scalar(details, "$.type") = "AUDIT"
Deep nested access on known structure	Syntactic Sugar format (->)	Much more readable	resource -> data.placement.availabilityZone
Value that might be object/array/boolean	json_extract() + tostring()	json_extract_scalar returns null for non-leaf values	tostring(json_extract(debugContext, "$.debugData.errorCode"))

---

Known limitations summary

#	Limitation	Affected Function	Impact	Workaround
1	String values include surrounding quotes	json_extract()	Breaks filter comparisons	Use json_extract_scalar() for leaf values
2	Array elements retain quotes	json_extract_array()	Requires replex() workarounds	Use json_extract_scalar_array() for scalar arrays
3	Requires to_json_string() conversion	json_extract_scalar_array()	Extra conversion step required	Wrap input with to_json_string()
4	Cannot extract non-leaf values	json_extract_scalar()	Returns null for objects/arrays	Use json_extract() then json_extract_scalar()
5	No reliable direct array index access	json_extract_scalar()	Requires 3-function chain	Use json_extract_array() + arrayindex()
6	Cannot directly assign to XDM array fields	json_extract_array()	Quoted values in XDM fields	Use json_extract_scalar_array() or arraymap()

---

JSONPath Quick Reference

Expression	Description
$	Root element
$.key	Child element named key
$.a.b	Nested child: b inside a
$.array[0]	First element of an array
$.array[*]	All elements of an array
$.array[0].field	Field field of the first array element
$[key]	Bracket notation for keys with special characters
$['key.name']	Bracket notation with quotes for dotted keys
$[@field]	Bracket notation for keys starting with @

---

Reference: Cortex XSIAM — XQL Language — JSON Functions