json_extract_scalar_array

Learn more about the Cortex Query Language json_extract_scalar_array() function.

Important

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

Syntax

json_extract_scalar_array(<json_array_string>, <json_path>)

Important

A field in the <json_path> that contains characters, such as a dot (.) or colon (:) and should be escaped as it's an invalid JSON path, is currently unsupported.

Description

The json_extract_scalar_array() function accepts a string representing a JSON array, and returns an XQL-native array. This function is equivalent to the json_extract_array except that the final output isn't displayed in double quotes ("..."). 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.

Example

Extract the first IPV4 address found in the first element of the agent_interface_map array. The values of the IPv4 addresses in the array will not contain any double quotes.

dataset = xdr_data 
| fields agent_interface_map as aim 
| alter ipv4 = json_extract_scalar_array(to_json_string(arrayindex(aim, 0)) , "$.ipv4") 
| filter aim != null 
| limit 10

Final output with 1 row from the results table. Notice that the IPV4 column doesn't contain any double quotes (" ") around the IP address 172.16.15.42:

_TIME	AIM	_PRODUCT	_VENDOR	INSERT_TIMESTAMP	IPV4
Aug 9th 2023 10:04:39	[{"ipv4":["172.16.15.42"], "ipv6": [], "mac": "00:50:56:9f:30:a9"}]	XDR agent	PANW	Aug 17th 2023 19:25:48	172.16.15.42

In contrast, compare the above results to the same query using the json_extract_array() function. The final output with the same row from the results table has in the IPV4 column the IP address in double quotes "172.16.15.42".

_TIME	AIM	_PRODUCT	_VENDOR	INSERT_TIMESTAMP	IPV4
Aug 9th 2023 10:04:39	[{"ipv4":["172.16.15.42"], "ipv6": [], "mac": "00:50:56:9f:30:a9"}]	XDR agent	PANW	Aug 17th 2023 19:25:48	"172.16.15.42"