tencent cloud

Feedback

JSON Functions

Last updated: 2022-04-18 15:27:18

    This document introduces the basic syntax and examples of JSON functions.

    FunctionSyntaxDescription
    json_array_containsjson_array_contain(x, value)Determines whether a JSON array contains a given value.
    json_array_getjson_array_get(x, index)Returns the element with the specified index in a given JSON array.
    json_array_lengthjson_array_length(x) Returns the number of elements in a given JSON array.
    json_extractjson_extract(x, json_path)Extracts a set of JSON values (array or object) from a JSON object or array.
    json_extract_scalarjson_extract_scalar(x, json_path)Extracts a set of scalar values (strings, integers, or Boolean values) from a JSON object or array. Similar to the `json_extract` function.
    json_formatjson_format(x)Converts a JSON value into a string value.
    json_parsejson_parse(x)Converts a string value into a JSON value.
    json_sizejson_size(x, json_path)Calculates the number of elements in a JSON object or array.

    json_array_contains

    The json_array_contains function is used to determine whether a JSON array contains a specified value.

    Syntax

    json_array_contains(x, value)
    

    Field description

    Parameter Description
    x The parameter value is a JSON array.
    value Value.

    Return value type

    Boolean

    Example

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

    • Search and analysis statement

      * | SELECT json_array_contains('[1, 2, 3]', 2)
      
    • Search and analysis result

    json_array_get

    The json_array_get function is used to get the element with a specified index in a JSON array.

    Syntax

    json_array_get(x, index)
    

    Field description

    Parameter Description
    x The parameter value is a JSON array.
    index JSON subscript (index), starting from 0.

    Return value type

    Varchar

    Example

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

    • Search and analysis statement

      * | SELECT json_array_get('["a", [3, 9], "c"]', 1)
      
    • Search and analysis result

    json_array_length

    The json_array_length function is used to calculate the number of elements in a JSON array.

    Syntax

    json_array_length(x)
    

    Field description

    Parameter Description
    x The parameter value is a JSON array.

    Return value type

    Bigint

    Example

    • Example 1. Calculate the number of JSON elements in the apple.message field

      • Field sample
        apple.message:[{"traceName":"StoreMonitor"},{"topicName":"persistent://apache/pulsar/test-partition-17"},{"producerName":"pulsar-mini-338-36"},{"localAddr":"pulsar://pulsar-mini-broker-5.pulsar-mini-broker.pulsar.svc.cluster.local:6650"},{"sequenceId":826},{"storeTime":1635905306062},{"messageId":"19422-24519"},{"status":"SUCCESS"}]
        
    • Search and analysis statement

      * | SELECT json_array_length(apple.message)
      
      
    • Search and analysis result

    json_extract

    The json_extract function is used to extract a set of JSON values (array or object) from a JSON object or array.

    Syntax

    json_extract(x, json_path)
    

    Field description

    Parameter Description
    x The parameter value is a JSON object or array.
    json_path JSONPath, such as $.store.book[0].title.
    Note: JSON syntax requiring array element traversal is not supported, such as the following: $.store.book[*].author, $..book[(@.length-1)], $..book[?(@.price<10)].

    Return value type

    JSON string

    Example

    Get the value of epochSecond in the apple.instant field.

    • Field sample

      apple.instant:{"epochSecond":1635905306,"nanoOfSecond":63001000}
      
    • Search and analysis statement

      * | SELECT json_extract(apple.instant, '$.epochSecond')
      
      
    • Search and analysis result

    json_extract_scalar

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

    Syntax

    json_extract_scalar(x, json_path)
    

    Field description

    Parameter Description
    x The parameter value is a JSON array.
    json_path JSONPath, such as $.store.book[0].title.
    Note: JSON syntax requiring array element traversal is not supported, such as the following: $.store.book[*].author, $..book[(@.length-1)], $..book[?(@.price<10)].

    Return value type

    Varchar

    Example

    Get the value of epochSecond from the apple.instant field and convert the value into a bigint value for summation.

    • Field sample

      apple.instant:{"epochSecond":1635905306,"nanoOfSecond":63001000}
      
    • Search and analysis statement

      * | SELECT sum(cast(json_extract_scalar(apple.instant,'$.epochSecond') AS bigint) )
      
    • Search and analysis result

    json_format

    The json_format function is used to convert a JSON value into a string value.

    Syntax

    json_format(x)
    

    Field description

    Parameter Description
    x The parameter value is of JSON type.

    Return value type

    Varchar

    Example

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

    • Search and analysis statement

      * | SELECT json_format(json_parse('[1, 2, 3]'))
      
    • Search and analysis result

    json_parse

    The json_parse function is used to convert a string value into a JSON value and determine whether it complies with the JSON format.

    Syntax

    json_parse(x)
    

    Field description

    Parameter Description
    x The parameter value is a string.

    Return value type

    JSON

    Example

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

    • Search and analysis statement

      * | SELECT json_parse('[1, 2, 3]')
      
    • Search and analysis result

    json_size

    The json_size function is used to calculate the number of elements in a JSON object or array.

    Syntax

    json_size(x, json_path)
    

    Field description

    Parameter Description
    x The parameter value is a JSON object or array.
    json_path JSON path, in the format of $.store.book[0].title.

    Return value type

    Bigint

    Example

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

    • Search and analysis statement

      * | SELECT json_size(json_parse('[1, 2, 3]'))
      
    • Search and analysis result

    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support