JSON functions - Simple Log Service - Alibaba Cloud Documentation Center

This topic outlines the syntax and usage of JSON functions, including examples.

The table below lists the JSON functions supported by Simple Log Service.

Important

To use strings in analytic statements, enclose them in single quotation marks (''). Unenclosed strings or those in double quotation marks ("") represent field or column names. For instance, 'status' denotes the string status, while status or "status" refers to the log field status.
When expanding a log field of the JSON type into multiple rows, UNNEST clauses are recommended. For more information, see the referenced document.
If a string cannot be parsed into JSON data, null is returned.
If JSON logs are truncated during collection, using a JSON function to query and analyze the logs will result in an error and halt the process. To address this, include a try function in the query statement to capture the error, allowing the process to continue. For example, * | select message, try(json_parse(message)). For more information, see the referenced document.

Function name	Syntax	Description	Support for SQL	Support for SPL
json_array_contains function	json_array_contains(x, value)	Checks whether a JSON array contains a specified value.	√	√
json_array_get function	json_array_get(x, index)	Obtains the element that corresponds to an index in a JSON array.	√	×
json_array_length function	json_array_length(x)	Obtains the number of elements in a JSON array.	√	√
json_extract function	json_extract(x, json_path)	Obtains a set of JSON values from a JSON object or a JSON array. The JSON values can be of the array or object type.	√	√
json_extract_scalar function	json_extract_scalar(x, json_path)	Obtains a set of scalar values from a JSON object or a JSON array. The scalar values can be of the string, integer, or Boolean type. This function is similar to the json_extract function.	√	√
json_extract_bool function	json_extract_bool(x, json_path)	Obtains a Boolean value from a JSON object or a JSON array.	√	×
json_extract_long function	json_extract_long(x, json_path)	Obtains a bigint value from a JSON object or a JSON array.	√	×
json_extract_double function	json_extract_double(x, json_path)	Obtains a double value from a JSON object or a JSON array.	√	×
json_format function	json_format(x)	Converts JSON data to a string.	√	√
json_parse function	json_parse(x)	Converts a string to JSON data.	√	√
json_size function	json_size(x, json_path)	Obtains the number of elements in a JSON object or a JSON array.	√	√

json_array_contains function

The json_array_contains function checks if a JSON array includes a specified value.

Syntax

json_array_contains(x, value)

Parameter description

Parameter	Description
x	The parameter value is a JSON array.
value	Numeric value.

Return value type

Boolean.

Example

Determine if the JSON array [1, 2, 3] contains the number 2.

Query and analysis statement (Test)

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

Query and analysis result

json_array_get function

The json_array_get function retrieves the element at a specified index in a JSON array.

Syntax

json_array_get(x, index)

Parameter description

Parameter	Description
x	The parameter value is a JSON array.
index	The index within the JSON array, starting from 0.

Return value type

The varchar type.

Example

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

Query and analysis statement (Test)

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

Query and analysis result

json_array_length function

The json_array_length function determines the number of elements in a JSON array.

Syntax

json_array_length(x)

Parameter description

Parameter	Description
x	The parameter value is a JSON array.

Return value type

The bigint type.

Example

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

json_extract function

The json_extract function retrieves JSON values from a JSON object or array, which can be of the array or object type.

Important

If the JSON data is invalid, the json_extract function will return an error message. In such cases, it is recommended to use the json_extract_scalar function instead.

Syntax

json_extract(x, json_path)

Parameter description

Parameter	Description
x	The parameter value is a JSON object or JSON array.
json_path	The JSON path, in the format of $.store.book[0].title. For more information, see how to set json_path.

Return value type

JSON-formatted string.

Example

SQL

Retrieve the EndTime value from the Results field.

Sample field

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

Query and analysis statement

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

Query and analysis result

SPL

Retrieve the EndTime value from the Results field.

Sample field

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

SPL statement

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

SPL result

json_extract_scalar function

The json_extract_scalar function extracts scalar values from a JSON object or array, which can be strings, integers, or Boolean values.

Syntax

json_extract_scalar(x, json_path)

Parameter description

Parameter	Description
x	The parameter value is a JSON object or JSON array.
json_path	The JSON path, in the format of $.store.book[0].title. For more information, see how to set json_path.

Return value type

The varchar type.

Example

SQL

Retrieve the RawResultCount value from the Results field and convert it to a bigint type to perform summation.

Sample Field

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

Query and Analysis Statement

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

Query and Analysis Results

SPL

Retrieve the RawResultCount value from the Results field.

Sample field

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

SPL statement

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

SPL result

json_extract_bool function

The json_extract_bool function retrieves a Boolean value from a JSON object or array. If the conversion fails, null is returned.

Syntax

json_extract_bool(x, json_path)

Parameter description

Parameter	Description
x	The parameter value is of JSON type.
json_path	The JSON path, in the format of $.store.book[0].title. For more information, see How to set json_path.

Return value type

Boolean.

Example

Retrieve a Boolean value from the Results JSON array.

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

Query and analysis statement

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

Query and analysis result

json_extract_long function

The json_extract_long function retrieves a bigint value from a JSON object or array. If the conversion fails, null is returned.

Syntax

json_extract_long(x, json_path)

Parameter description

Parameter	Description
x	The parameter value is of JSON type.
json_path	The JSON path, in the format of $.store.book[0].title. For more information, see How to set json_path.

Return value type

The bigint type.

Example

Retrieve a bigint value from the Results JSON array.

Sample field

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

Query and analysis statement

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

Query and analysis result

json_extract_double function

The json_extract_double function retrieves a double value from a JSON object or array. If the conversion fails, null is returned.

Syntax

json_extract_double(x, json_path)

Parameter description

Parameter	Description
x	The parameter value is of JSON type.
json_path	The JSON path, in the format of $.store.book[0].title. For more information, see How to Set json_path.

Return value type

The double type.

Example

Retrieve 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

json_format function

The json_format function converts JSON data into a string format.

Syntax

json_format(x)

Parameter description

Parameter	Description
x	The parameter value is of JSON type.

Return value type

The varchar type.

Example

Convert the JSON array [1,2,3] to a string representation.

Query and analysis statement (Test)

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

Query and analysis result

json_parse function

The json_parse function converts a string to JSON data and verifies the string's JSON format. It is generally used for format validation rather than value extraction, for which the json_extract_scalar function is recommended.

Syntax

json_parse(x)

Parameter description

Parameter	Description
x	The parameter value is a string.

Return value type

JSON.

Example

SQL

Example 1

Convert the string [1,2,3] to the JSON array [1, 2, 3].
- Query and analysis statement (Test)
```
 * | SELECT json_parse('[1, 2, 3]')
```
- Query and analysis result
Example 2

Extract subfields from the logging field.
- Sample field
- Query and analysis statement (Test)
```
*| SELECT map_keys(try_cast(json_parse(logging) AS map(varchar, json)))
```
- Query and analysis result

SPL

Transform the string [1,2,3] into the JSON array [1, 2, 3].

SPL statement

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

SPL result

json_size function

The json_size function calculates the number of elements within a JSON object or array.

Syntax

json_size(x, json_path)

Parameter description

Parameter	Description
x	The parameter value is a JSON object or JSON array.
json_path	The JSON path, format is $.store.book[0].title. For more information, see How to set json_path.

Return value type

The bigint type.

Example

Retrieve the count of elements within the status field.

Sample field

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

Query and analysis statement

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

Query and analysis result