This topic describes the syntax of JSON functions. This topic also provides examples on how to use the functions.
The following table describes the JSON functions that are supported by Simple Log Service.
If you want to use strings in analytic statements, you must enclose the strings in single quotation marks (''). Strings that are not enclosed or strings that are enclosed in double quotation marks ("") indicate field names or column names. For example,
'status'indicates the status string, andstatusor"status"indicates the status log field.If the value of a log field is of the JSON type and needs to be expanded to multiple rows, we recommend that you use UNNEST clauses. For more information, see UNNEST clause.
If a string fails to be parsed into JSON data, null is returned.
If JSON logs are truncated during the collection process, the system returns an error and stops the query and analysis process when you use a JSON function to query and analyze the JSON logs. To troubleshoot the error, you can specify a try expression in a query statement to capture the error. Then, the system can continue the query and analysis process. Example:
* | select message, try(json_parse(message)). For more information, see TRY function.
Function | Syntax | Description | Supported in SQL | Supported in SPL |
json_array_contains(x, value) | Checks whether a JSON array contains a specified value. | √ | √ | |
json_array_get(x, index) | Obtains the element that corresponds to an index in a JSON array. | √ | × | |
json_array_length(x) | Obtains the number of elements in a JSON array. | √ | √ | |
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(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_format(x) | Converts JSON data to a string. | √ | √ | |
json_parse(x) | Converts a string to JSON data. | √ | √ | |
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 whether a JSON array contains a specified value.
Syntax
json_array_contains(x, value)Parameters
Parameter | Description |
x | The value of this parameter is a JSON array. |
value | The numeric value. |
Return value type
The Boolean type.
Examples
Check whether the [1, 2, 3] JSON array contains 2.
Query statement (Debug)
* | SELECT json_array_contains('[1, 2, 3]', 2)Query and analysis results
json_array_get function
The json_array_get function obtains the element that corresponds to an index in a JSON array.
Syntax
json_array_get(x, index)Parameters
Parameter | Description |
x | The value of this parameter is a JSON array. |
index | The JSON index. The value of this parameter starts from 0. |
Return value type
The varchar type.
Examples
Obtain the element that corresponds to the index 1 in the ["a", [3, 9], "c"] JSON array.
Query statement (Debug)
* | SELECT json_array_get('["a", [3, 9], "c"]', 1)Query and analysis results
json_array_length function
The json_array_length function obtains the number of elements in a JSON array.
Syntax
json_array_length(x)Parameters
Parameter | Description |
x | The value of this parameter is a JSON array. |
Return value type
The bigint type.
Examples
Example 1: Obtain the number of JSON elements in the value of the Results field.
Sample field
Results:[{"EndTime":1626314920},{"FireResult":2}]Query statement
* | SELECT json_array_length(Results)Query and analysis results
Example 2: Obtain the number of JSON elements in the value of the time field.
Sample field
time:["time_local","request_time","upstream_response_time"]Query statement
* | SELECT json_array_length(time)Query and analysis results
json_extract function
The json_extract function 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.
If the data that you use the json_extract function to process is of an invalid JSON type, an error is reported. In this case, we recommend that you use the json_extract_scalar function.
Syntax
json_extract(x, json_path)Parameters
Parameter | Description |
x | The value of this parameter is a JSON object or a JSON array. |
json_path | The JSON path. Format: $.store.book[0].title. For more information, see How do I configure json_path? |
Return value type
The JSON string type.
Examples
SQL
Obtain 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 and analysis results
SPL
Obtain 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 results
json_extract_scalar function
The json_extract_scalar function 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.
Syntax
json_extract_scalar(x, json_path)Parameters
Parameter | Description |
x | The value of this parameter is a JSON object or a JSON array. |
json_path | The JSON path. Format: $.store.book[0].title. For more information, see How do I configure json_path? |
Return value type
The varchar type.
Examples
SQL
Obtain the value of the RawResultCount field from the Results field. Then, convert the value to the bigint type for summation.
Sample field
Results:[{"EndTime":1626314920},{"RawResultCount":1}]Query statement
* | SELECT sum(cast(json_extract_scalar(Results,'$.1.RawResultCount') AS bigint) )Query and analysis results
SPL
Obtain 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 results
json_format function
The json_format function converts JSON data to a string.
Syntax
json_format(x)Parameters
Parameter | Description |
x | The value of this parameter is of the JSON type. |
Return value type
The varchar type.
Examples
Convert the [1,2,3] JSON array to the [1, 2, 3] string.
Query statement (Debug)
* | SELECT json_format(json_parse('[1, 2, 3]'))Query and analysis results
json_parse function
The json_parse function only converts a string to JSON data and checks whether the string is in the JSON format. In most cases, the json_parse function is insignificant. If you want to extract values from JSON data, we recommend that you use the json_extract_scalar function.
Syntax
json_parse(x)Parameters
Parameter | Description |
x | The value of this parameter is a string. |
Return value type
The JSON type.
Examples
SQL
Example 1
Convert the [1,2,3] string to the [1, 2, 3] JSON array.
Query statement (Debug)
* | SELECT json_parse('[1, 2, 3]')Query and analysis results
Example 2
Extract the subfields from the
loggingfield.Sample field
Query statement (Debug)
*| SELECT map_keys(try_cast(json_parse(logging) AS map(varchar, json)))Query and analysis results
SPL
Convert the [1,2,3] string to the [1, 2, 3] JSON array.
SPL statement
* | extend a = json_parse('[1, 2, 3]')SPL results
json_size function
The json_size function obtains the number of elements in a JSON object or a JSON array.
Syntax
json_size(x, json_path)Parameters
Parameter | Description |
x | The value of this parameter is a JSON object or a JSON array. |
json_path | The JSON path. Format: $.store.book[0].title. For more information, see How do I configure json_path? |
Return value type
The bigint type.
Examples
Obtain 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 and analysis results
