This topic describes the syntax of JSON functions. This topic also provides examples on how to use JSON functions.

The following table describes the JSON functions that are supported by Log Service.

Important
  • 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, 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 an UNNEST clause. 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 your 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.
FunctionSyntaxDescription
json_array_contains functionjson_array_contains(x, value)Checks whether a JSON array contains a specified value.
json_array_get functionjson_array_get(x, index)Obtains the element that corresponds to an index in a JSON array.
json_array_length functionjson_array_length(x)Obtains the number of elements in a JSON array.
json_extract functionjson_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 functionjson_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 functionjson_format(x)Converts JSON data to a string.
json_parse functionjson_parse(x)Converts a string to JSON data.
json_size functionjson_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

ParameterDescription
xThe value of this parameter is a JSON array.
valueThe numeric value.

Return value type

The Boolean type.

Examples

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

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

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

ParameterDescription
xThe value of this parameter is a JSON array.
indexThe 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
    * | SELECT json_array_get('["a", [3, 9], "c"]', 1)
  • Query and analysis resultsjson_array_get

json_array_length function

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

Syntax

json_array_length(x)

Parameters

ParameterDescription
xThe 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 resultsjson_array_length
  • 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 resultsjson_array_length

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.
Important 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

ParameterDescription
xThe value of this parameter is a JSON object or a JSON array.
json_pathThe 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

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 resultsjson_extract

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

ParameterDescription
xThe value of this parameter is a JSON object or a JSON array.
json_pathThe JSON path. Format: $.store.book[0].title. For more information, see How do I configure json_path?

Return value type

The varchar type.

Examples

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

json_format function

The json_format function converts JSON data to a string.

Syntax

json_format(x)

Parameters

ParameterDescription
xThe 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 resultsjson_format

json_parse function

The json_parse function converts a string to JSON data. The function checks whether the string is in the JSON format in advance. 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

ParameterDescription
xThe value of this parameter is a string.

Return value type

The JSON type.

Examples

  • Example 1

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

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

    Extract the subfields from the etl_context field.

    • Sample fieldjson_parse
    • Query statement
      *| SELECT map_keys(try_cast(json_parse(etl_context) AS map(varchar, json)))
    • Query and analysis resultsjson_parse

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

ParameterDescription
xThe value of this parameter is a JSON object or a JSON array.
json_pathThe 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 resultsjson_size