Help Center/ Log Tank Service/ User Guide/ Log Search and Analysis/ Using SQL Analysis Syntax/ SQL JSON Functions
Updated on 2025-03-24 GMT+08:00
View PDF

SQL JSON Functions

Description

JSON functions are used to parse JSON objects or JSON arrays and extract values from them.

Syntax

SELECT json_extract(Results, '$.[0].EndTime')

JSON Function Statements

Table 1 JSON function statements

Statement

Description

Example

Returned Value Type

json_extract

Extracts a set of JSON values (object or array) from a JSON object or JSON array.

json_extract(x, json_path)

Data of string type in JSON format

json_extract_scalar

Extracts a set of scalar values (string, integer, or Boolean) from a JSON object or JSON array. If the specified JSON path is not a scalar, null is returned.

json_extract_scalar(x,json_path)

varchar type

Examples

  • json_extract function

    Obtain the value of the EndTime field in the Results field.

    1. Example field 
      Results:[{"EndTime":1626314520},{"FireResult":2}]
    2. Query and analysis statement 
      SELECT json_extract(Results, '$.[0].EndTime')
    3. Query and analysis result
      Table 2 Query and analysis result

      EXPR$0

      1626314520
  • json_extract_scalar function

    Obtain the values of the RawResultCount field from the Results field, convert these values to bigint type, and sum them up.

    1. Example field 
      Results:[{"EndTime":1626314520},{"RawResultCount":1}]
    2. Query and analysis statement 
      SELECT sum(cast(json_extract_scalar(Results,'$.[1].RawResultCount') AS bigint) )
    3. Query and analysis result
      Table 3 Query and analysis result

      EXPR$0

      1546