JSON functions

This topic covers the basic syntax of JSON functions with examples.

SLS supports the following JSON functions.

Important

In Log Service query statements, enclose strings in single quotation marks (''). Log Service interprets unquoted characters or characters in double quotation marks ("") as a field name or column name. For example, 'status' represents the string status, while status or "status" represents the log field named status.
To expand a JSON-formatted log field value into multiple rows, use the UNNEST clause. For more information, see UNNEST clause.
If a string fails to be parsed as JSON, the function returns null.
If a JSON log is truncated during collection, analyzing it with a JSON function returns an error and aborts the query. To handle this error, wrap the function call in a TRY expression to capture the exception and allow the query to continue. For example, * | select message, try(json_parse(message)). For more information, see TRY expression.

Function name	Syntax	Description	SQL support	SPL support
json_array_contains function	json_array_contains(x, value)	Checks if a JSON array contains a specific value.	√	√
json_array_get function	json_array_get(x, index)	Returns the element at a specified index in a JSON array.	√	×
json_array_length function	json_array_length(x)	Returns the number of elements in a JSON array.	√	√
json_extract function	json_extract(x, json_path)	Extracts a JSON object or array from a JSON object or JSON array.	√	√
json_extract_scalar function	json_extract_scalar(x, json_path)	Extracts a scalar value, such as a string, integer, or boolean value, from a JSON object or JSON array.	√	√
json_extract_bool function	json_extract_bool(x, json_path)	Extracts a boolean value from a JSON object or JSON array.	√	×
json_extract_long function	json_extract_long(x, json_path)	Extracts a bigint value from a JSON object or JSON array.	√	×
json_extract_double function	json_extract_double(x, json_path)	Extracts a double value from a JSON object or JSON array.	√	×
json_format function	json_format(x)	Formats a JSON value into a string.	√	√
json_parse function	json_parse(x)	Parses a string into a JSON value.	√	√
json_size function	json_size(x, json_path)	Returns the number of elements in a JSON array or the number of key-value pairs in a JSON object.	√	√
json_object_flatten function	json_object_flatten(x)	Flattens a JSON object into a single-layer key-value structure.	√	×

Json_array_contains

The json_array_contains function checks whether a JSON array contains a specific value.

Syntax

json_array_contains(x, value)

Parameters

Parameter	Description
x	A JSON array.
value	The value to search for in the JSON array.

Return value type

boolean

Example

Check whether the JSON array [1, 2, 3] contains the value 2.

Query statement (Test)

* | SELECT json_array_contains('[1, 2, 3]', 2)

The query returns true because the JSON array [1, 2, 3] contains 2.

json_array_get function

Returns the element at a specified index in a JSON array.

Syntax

json_array_get(x, index)

Parameters

Parameter	Description
x	A JSON array.
index	The zero-based index of the element.

Return type

varchar

Example

Returns the element at index 1 from the JSON array ["a", [3, 9], "c"].

Query statement (Test)

* | SELECT json_array_get('["a", [3, 9], "c"]', 1)

The query result is [3,9].

json_array_length function

Counts the elements in a JSON array.

Syntax

json_array_length(x)

Parameters

Parameter	Description
x	The source JSON array.

Return value type

bigint

Examples

Example 1: Counts the elements of the JSON array in the Results field.
- Sample field
```
Results:[{"EndTime":1626314920},{"FireResult":2}]
```
- Query and analysis statement
```
* | SELECT json_array_length(Results)
```
- The result is 2.
Example 2: Counts the elements of the JSON array in the time field.
- Sample field
```
time:["time_local","request_time","upstream_response_time"]
```
- Query and analysis statement
```
* | SELECT json_array_length(time)
```
- The result is 3.

Json_extract function

The json_extract function extracts a JSON value, such as a JSON object or a JSON array, from a JSON object or JSON array.

Important

The json_extract function returns an error for invalid JSON types. Use the json_extract_scalar function instead.

Syntax

json_extract(x, json_path)

Parameters

Parameter	Description
x	A JSON object or JSON array.
json_path	A JSON path, such as `$.store.book[0].title`. For details, see Set the json_path.

Return value type

A JSON-formatted string.

Examples

SQL

Extract the value of the EndTime field from the Results field.

Sample data

Results:[{"EndTime":1626314920},{"FireResult":2}]

Query statement

* | SELECT json_extract(Results, '$.0.EndTime')

The query returns 1626314920.

SPL

Extract the value of the EndTime field from the Results field.

Sample data

Results:[{"EndTime":1626314920},{"FireResult":2}]

SPL statement

* | extend a = json_extract(Results, '$.0.EndTime')

SPL result

The result includes the field a set to 1626314920.

json_extract_scalar

The json_extract_scalar function extracts a scalar value (string, integer, or Boolean) from a JSON object or JSON array.

Syntax

json_extract_scalar(x, json_path)

Parameters

Parameter	Description
x	A JSON object or JSON array.
json_path	The JSON path, such as $.store.book[0].title. For more information, see Set the json_path.

Return type

varchar

Examples

SQL

Extract the value of the RawResultCount field from the Results field, cast the value to bigint, and then calculate the sum.

Sample data

Results:[{"EndTime":1626314920},{"RawResultCount":1}]

Query statement

* | SELECT sum(cast(json_extract_scalar(Results,'$.1.RawResultCount') AS bigint) )

The query result is 288.

SPL

Extract the value of the RawResultCount field from the Results field.

Sample data

Results:[{"EndTime":1626314920},{"RawResultCount":1}]

SPL statement

* | extend a = json_extract_scalar(Results, '$.1.RawResultCount')

The value of the a field is 1.

json_extract_bool

Extracts a boolean value from a JSON object or JSON array. Returns null if the extraction fails.

Syntax

json_extract_bool(x, json_path)

Parameters

Parameter	Description
x	A JSON object or a JSON array.
json_path	The JSON path, such as $.store.book[0].title. For more information, see Set the json_path.

Return value type

boolean

Example

Extract a boolean value from the Results JSON array.

Sample field
```
Results:[{"ret":true},{"status":FALSE}]
```

Query statement

* | SELECT json_extract_bool(Results, '$.0.ret')

Query result

The query returns true.

json_extract_long

Extracts a bigint value from a JSON object or JSON array. Returns null if the extraction fails.

Syntax

json_extract_long(x, json_path)

Parameters

Parameter	Description
x	A JSON value.
json_path	The JSON path, such as $.store.book[0].title. For more information, see Set the json_path.

Return value

bigint

Example

This example extracts a bigint value from the Results JSON array.

Sample field

Results:[{"EndTime":1626314920},{"FireResult":2}]

Query statement

* | SELECT json_extract_long(Results, '$.0.EndTime')

Query result

Returns 1626314920.

Json_extract_double

Extracts a double value from a JSON object or JSON array. Returns null if the extraction fails.

Syntax

json_extract_double(x, json_path)

Parameters

Parameter	Description
x	A JSON object or JSON array.
json_path	The JSON path, such as $.store.book[0].title. For details, see How to set json_path.

Return type

double

Example

Extract a double value from the Results JSON array.

Sample field

Results:[{"EndTime":1626314920},{"FireResult":2}]

Query and analysis statement

* | SELECT json_extract_double(Results, '$.0.EndTime')

Query and analysis result

The query returns 1626314920.0.

json_format function

The json_format function formats a JSON value as a string.

Syntax

json_format(x)

Parameters

Parameter	Description
x	A JSON value.

Return type

Varchar.

Example

Converts the JSON array [1,2,3] to the string '[1, 2, 3]'.

Query statement (Test)

* | SELECT json_format(json_parse('[1, 2, 3]'))

The query result is '[1, 2, 3]'.

json_parse function

The json_parse function converts a string to the JSON type, validating its format. However, this function has limited use. To extract values from a JSON string, use the json_extract_scalar function.

Syntax

json_parse(x)

Parameters

Parameter	Description
x	A string expression.

Return value type

JSON type.

Examples

SQL

Example 1

Converts the string '[1, 2, 3]' to the JSON array [1, 2, 3].
- Query statement (Test)
```
 * | SELECT json_parse('[1, 2, 3]')
```
- The query result is [1, 2, 3].
Example 2

Extracts the keys from the logging field.
- This example processes the logging field, which is a JSON object that contains key-value pairs such as message, processName, thread, threadName, module, funcName, levelname, and process.
- Query statement (Test)
```
*| SELECT map_keys(try_cast(json_parse(logging) AS map(varchar, json)))
```
- The query result is ["funcName","levelname","message","module","process","processName","thread","threadName"].

SPL

Converts the string '[1, 2, 3]' to the JSON array [1, 2, 3].

SPL statement

 * | extend a = json_parse('[1, 2, 3]')

SPL result

The value of the a field is [1, 2, 3].

json_size function

The json_size function returns the number of elements in a JSON object or JSON array.

Syntax

json_size(x, json_path)

Parameters

Parameter	Description
x	A JSON object or JSON array.
json_path	The JSON path, such as $.store.book[0].title. For more information, see Set the json_path.

Return value type

bigint

Example

This example calculates the number of elements in the RawResults field.

Sample field

Results:[{"EndTime":1626314920,"FireResult":2,"RawResults":[{"_col0":"1094"}]}]

Query statement

* | SELECT json_size(Results, '$.0.RawResults')

The query result is 1.

json_object_flatten function

Flattens a JSON object into a single-layer key-value structure.

Syntax

json_object_flatten(x)

Parameters

Parameter	Description
x	Specifies the JSON value to flatten. The function returns null if the input is not a JSON object.

Return value type

map(varchar, varchar)

Example

Flattens the JSON object in the 'content' field into a single-layer key-value structure.

Sample field

content: '{"Time":1626314920,"Info":[{"count":"1"}],"Body":"this is test"}'

Query statement

select json_object_flatten(content) as data from  (values '{"Time":1626314920,"Info":[{"count":"1"}],"Body":"this is test"}') t (content) limit 1;

Output

The value of the data field is {"Time":"1626314920","Info":"[{\"count\":\"1\"}]","Body":"this is test"}. This shows that the original JSON object has been flattened into a single-layer key-value structure.