All Products
Search
Document Center

Simple Log Service:JSON functions

Last Updated:Feb 27, 2024

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.

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 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 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_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 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 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

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

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

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 resultsjson_extract

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

image.png

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 resultsjson_extract_scalar

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 resultsimage.png

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 resultsjson_format

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 resultsjson_format

  • Example 2

    Extract the subfields from the logging field.

    • Sample fieldjson_parse

    • Query statement (Debug)

      *| SELECT map_keys(try_cast(json_parse(logging) AS map(varchar, json)))
    • Query and analysis resultsjson_parse

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

image.png

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 resultsjson_size