This topic describes how to use the string function JSON_VALUE in Realtime Compute for Apache Flink.

Syntax

VARCHAR JSON_VALUE(VARCHAR content, VARCHAR path)

Input parameters

  • content

    The JSON object that you want to parse, which is represented as a string. This parameter is of the VARCHAR type.

  • path
    The path expression that is used to parse the JSON object. This parameter is of the VARCHAR type. The following table describes the path expressions that are supported by the JSON_VALUE function.
    Symbol Description
    $ The root object.
    [] The array subscript.
    * The array wildcard.
    . The child element.

Description

Extracts the value of the specific path from a JSON string. If the JSON string is invalid or an input parameter is null, null is returned.

Example

  • Test data
    id(INT) json(VARCHAR) path1(VARCHAR)
    1 [10, 20, [30, 40]] $[2][*]
    2 null $.ccc.hhh[*]
    3 {"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"} $.ccc.hhh[1]
    4 [10, 20, [30, 40]] NULL
    5 NULL $[2][*]
    6 "{xx]" "$[2][*]"
  • Test statement
    SELECT 
        id,
        JSON_VALUE(json, path1) AS `value`
    FROM 
        T1;
  • Test results
    id (INT) value (VARCHAR)
    1 [30,40]
    2 ["h0","h1","h2"]
    3 h1
    4 NULL
    5 NULL
    6 NULL