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 Log Service.

Notice
  • If you want to use strings in analytic statements, you must enclose strings in single quotation marks (''). Strings that are not enclosed or enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "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 the UNNEST function. For more information, see UNNEST function.
  • If a string fails to be parsed into JSON data, null is returned.
Function Syntax Description
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) Calculates the number of elements in a JSON array.
json_extract function json_extract(x,json_path) Extracts a set of JSON values from a JSON object or a JSON array.
json_extract_scalar function json_extract_scalar(x,json_path) Extracts a set of scalar values (strings, integers, or Boolean values) from a JSON object or a JSON array. This function is similar to the json_extract function.
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) Calculates the number of elements in a JSON object or a JSON array.

json_array_contains function

The json_array_contains function is used to check 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 string contains 2.

  • Query statement
    * | SELECT json_array_contains('[1, 2, 3]', 2)
  • Query and analysis resultjson_array_contains

json_array_get function

The json_array_get function is used to obtain 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

Return the element that corresponds to the index 1 in the ["a", [3, 9], "c"] JSON array.

  • Query statement
    * | SELECT json_array_get('["a", [3, 9], "c"]', 1)
  • Query and analysis resultjson_array_get

json_array_length function

The json_array_length function is used to calculate 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: Calculate 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 resultjson_array_length
  • Example 2: Calculate 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 resultjson_array_length

json_extract function

The json_extract function is used to extract a set of JSON values from a JSON object or a JSON array.
Notice If the JSON data is invalid when you use the json_extract function, an error message appears. 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.

Return value type

The string type in the JSON format.

Examples

Extract 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 resultjson_extract

json_extract_scalar function

The json_extract_scalar function is used to extract a set of scalar values (strings, integers, or Boolean values) from a JSON object or a JSON array.

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.

Return value type

The varchar type.

Examples

Extract 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,'$.0.RawResultCount') AS bigint) )
  • Query and analysis resultjson_extract_scalar

json_format function

The json_format function is used to convert 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
    * | SELECT json_format(json_parse('[1, 2, 3]'))
  • Query and analysis resultjson_format

json_parse function

The json_parse function is used only to convert a string to JSON data and check whether the string matches the JSON format. In most cases, the json_parse function is of little significance. 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

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

  • Query statement
     * | SELECT json_parse('[1, 2, 3]')
  • Query and analysis resultjson_format

json_size function

The json_size function is used to calculate 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.

Return value type

The bigint type.

Examples

Calculate 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 resultjson_size