This topic describes the syntax of JSON functions and provides examples.
Simple Log Service supports the following JSON functions.
In Simple Log Service query statements, you must enclose strings in single quotation marks (''). Characters that are not enclosed in single quotation marks, or are enclosed in double quotation marks (""), are treated as field or column names. For example,
'status'represents the string 'status', whereasstatusor"status"represents the log field named 'status'.If the value of a log field is in JSON format and you need to expand it into multiple rows, use the UNNEST syntax. For more information, see UNNEST clause.
If a string cannot be parsed as JSON, the function returns null.
If a JSON log is truncated during collection, using a JSON function on it causes an error and aborts the query. To handle this error, you can use a TRY expression to catch the exception. This allows 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(x, value) | Checks whether a JSON array contains a specific value. | √ | √ | |
json_array_get(x, index) | Returns the element at a specified index in a JSON array. | √ | × | |
json_array_length(x) | Calculates the number of elements in a JSON array. | √ | √ | |
json_extract(x, json_path) | Extracts a set of JSON values, which can be an array or an object, from a JSON object or JSON array. | √ | √ | |
json_extract_scalar(x, json_path) | Extracts a set of scalar values, such as strings, integers, or Booleans, from a JSON object or JSON array. This function is similar to the json_extract function. | √ | √ | |
json_extract_bool(x, json_path) | Extracts a Boolean value from a JSON object or JSON array. | √ | × | |
json_extract_long(x, json_path) | Extracts a bigint value from a JSON object or JSON array. | √ | × | |
json_extract_double(x, json_path) | Extracts a double value from a JSON object or JSON array. | √ | × | |
json_format(x) | Converts a JSON object to a string. | √ | √ | |
json_parse(x) | Converts a string to a JSON object. | √ | √ | |
json_size(x, json_path) | Calculates the number of elements in a JSON object or array. | √ | √ | |
json_object_flatten(x) | Flattens a JSON object into a single-layer key-value structure. | √ | × |
json_array_contains function
The json_array_contains function checks whether a JSON array contains a specific value.
Syntax
json_array_contains(x, value)Parameters
Parameter | Description |
x | The parameter value is a JSON array. |
value | A numeric value. |
Return value type
boolean
Example
This example checks whether the JSON array [1, 2, 3] contains the value 2.
Query statement (Test)
* | SELECT json_array_contains('[1, 2, 3]', 2)Query result
json_array_get function
The 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 | The parameter value is a JSON array. |
index | The index of the JSON element. The index starts from 0. |
Return value type
varchar
Example
This 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)Query result
json_array_length function
The json_array_length function calculates the number of elements in a JSON array.
Syntax
json_array_length(x)Parameters
Parameter | Description |
x | The parameter value is a JSON array. |
Return value type
bigint
Examples
Example 1: This example calculates the number of JSON elements in the Results field.
Sample field
Results:[{"EndTime":1626314920},{"FireResult":2}]Query statement
* | SELECT json_array_length(Results)Query result
Example 2: This example calculates the number of JSON elements in the time field.
Sample field
time:["time_local","request_time","upstream_response_time"]Query statement
* | SELECT json_array_length(time)Query result
json_extract function
The json_extract function extracts a set of JSON values, which can be an array or an object, from a JSON object or JSON array.
The json_extract function reports an error for invalid JSON types. In such cases, use the json_extract_scalar function instead.
Syntax
json_extract(x, json_path)Parameters
Parameter | Description |
x | The parameter value is 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
A string in the JSON format.
Examples
SQL
This example extracts the value of the EndTime field from the Results field.
Sample field
Results:[{"EndTime":1626314920},{"FireResult":2}]Query statement
* | SELECT json_extract(Results, '$.0.EndTime')Query result
SPL
This example extracts the value of the EndTime field 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 a scalar value, such as a string, integer, or Boolean, from a JSON object or JSON array.
Syntax
json_extract_scalar(x, json_path)Parameters
Parameter | Description |
x | The parameter value is 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
varchar
Examples
SQL
This example extracts the value of the RawResultCount field from the Results field and converts the value to the bigint type to calculate the sum.
Field examples
Results:[{"EndTime":1626314920},{"RawResultCount":1}]Query statement
* | SELECT sum(cast(json_extract_scalar(Results,'$.1.RawResultCount') AS bigint) )Query result
SPL
This example extracts the value of the RawResultCount field 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 extracts a Boolean value from a JSON object or JSON array. If the value cannot be extracted, null is returned.
Syntax
json_extract_bool(x, json_path)Parameters
Parameter | Description |
x | The parameter value is of the JSON type. |
json_path | The JSON path, such as $.store.book[0].title. For more information, see Set the json_path. |
Return value type
boolean
Example
This example extracts a Boolean value from the JSON array Results.
Sample field
Results:[{"ret":true},{"status":FALSE}]Query statement
* | SELECT json_extract_bool(Results, '$.0.ret')Query result
json_extract_long function
The json_extract_long function extracts a bigint value from a JSON object or JSON array. If the value cannot be extracted, null is returned.
Syntax
json_extract_long(x, json_path)Parameters
Parameter | Description |
x | The parameter value is of the JSON type. |
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 extracts a bigint value from the JSON array Results.
Sample field
Results:[{"EndTime":1626314920},{"FireResult":2}]Query statement
* | SELECT json_extract_long(Results, '$.0.EndTime')Query result
json_extract_double function
The json_extract_double function extracts a double value from a JSON object or JSON array. If the value cannot be extracted, null is returned.
Syntax
json_extract_double(x, json_path)Parameters
Parameter | Description |
x | The parameter value is of the JSON type. |
json_path | The JSON path, such as $.store.book[0].title. For more information, see Set the json_path. |
Return value type
double
Example
This example extracts a double value from the JSON array Results.
Sample field
Results:[{"EndTime":1626314920},{"FireResult":2}]Query statement
* | SELECT json_extract_double(Results, '$.0.EndTime')Query result
json_format function
The json_format function converts a JSON object to a string.
Syntax
json_format(x)Parameters
Parameter | Description |
x | The parameter value is of the JSON type. |
Return value type
varchar
Example
This 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]'))Query result
json_parse function
The json_parse function converts a string to the JSON type and validates that the string is in a valid JSON format. To extract a value from a JSON object, use the json_extract_scalar function.
Syntax
json_parse(x)Parameters
Parameter | Description |
x | The parameter value is a string. |
Return value type
JSON
Examples
SQL
Example 1
This example converts the string '[1,2,3]' to the JSON array [1, 2, 3].
Query statement (Test)
* | SELECT json_parse('[1, 2, 3]')Query result
Example 2
This example extracts each subfield from the
loggingfield.Sample field
Query statement (Test)
*| SELECT map_keys(try_cast(json_parse(logging) AS map(varchar, json)))Query result
SPL
This example converts the string [1,2,3] to 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 in a JSON object or JSON array.
Syntax
json_size(x, json_path)Parameters
Parameter | Description |
x | The parameter value is 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 returns the number of elements in the status field.
Sample field
Results:[{"EndTime":1626314920,"FireResult":2,"RawResults":[{"_col0":"1094"}]}]Query statement
* | SELECT json_size(Results, '$.0.RawResults')Query result
json_object_flatten function
The json_object_flatten function flattens a JSON object into a single-layer key-value structure.
Syntax
json_object_flatten(x)Parameters
Parameter | Description |
x | The parameter value is of the JSON type. If the JSON object is not an object, null is returned. |
Return value type
map(varchar, varchar)
Example
This example flattens the JSON content of the content field into single-layer key-value pairs.
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;Outputs
