AnalyticDB for MySQL:JSON functions

• JSON _ARRAY_CONTAINS: determines whether a JSON array contains the value specified by value.
• JSON_ARRAY_LENGTH: returns the length of a JSON array.
• JSON _EXTRACT: returns the value specified by jsonpath from a JSON array.
• JSON_SIZE: returns the size of a JSON object or a JSON array.
• JSON_KEYS: returns all the keys of a JSON object in a specified path.
• JSON_UNQUOTE: unquotes the value specified by json_value, escapes specific characters in json_value, and then returns the processing result.
• JSON_CONTAINS: determines whether a given candidate is contained within a JSON document or whether the candidate exists in a specified path within the JSON document.
• JSON_CONTAINS_PATH: determines whether a specified path exists in the given JSON document.

JSON _ARRAY_CONTAINS

json_array_contains(json, value)

• Description: This function determines whether a JSON array contains the value specified by value.
• Data type of the input value: NUMERIC, STRING, or BOOLEAN for value.
• Data type of the return value: BOOLEAN.
• Example:

  SELECT json_array_contains('[1, 2, 3]', 2);

  The following information is returned:

  +-------------------------------------+
  | json_array_contains('[1, 2, 3]', 2) |
  +-------------------------------------+
  |                                   1 |
  +-------------------------------------+

JSON_ARRAY_LENGTH

json_array_length(json)

• Description: This function returns the length of a JSON array.
• Data type of the input value: STRING or JSON.
• Data type of the return value: BIGINT.
• Example:

  SELECT json_array_length('[1, 2, 3]');

  The following information is returned:

  +--------------------------------+
  | json_array_length('[1, 2, 3]') |
  +--------------------------------+
  |                              3 |
  +--------------------------------+

JSON _EXTRACT

json_extract(json, jsonpath)

• Description: This function returns the value specified by jsonpath from a JSON array.
• Data type of the input value: STRING or JSON.
• Data type of the return value: JSON.
• Example:

  SELECT json_extract('[10, 20, [30, 40]]', '$.1');

  The following information is returned:

  +-------------------------------------------+
  | json_extract('[10, 20, [30, 40]]', '$.1') |
  +-------------------------------------------+
  | 20                                        |
  +-------------------------------------------+

JSON_SIZE

json_size(json, jsonpath)

• Description: This function returns the size of a JSON object or a JSON array specified by jsonpath from a JSON array.
  Note If jsonpath does not specify a JSON object or a JSON array, 0 is returned.
• Data type of the input value: STRING or JSON.
• Data type of the return value: BIGINT.
• Examples:
  • jsonpath specifies a JSON object. Sample statement:

    SELECT json_size('{"x":{"a":1, "b": 2}}', '$.x') as result;

    The following information is returned:

    +--------+
    | result |
    +--------+
    |      2 |
    +--------+

  • jsonpath does not specify a JSON object or a JSON array. Sample statement:

    SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a') as result;

    The following information is returned:

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+

JSON_KEYS

json_keys(json, jsonpath)
json_keys(json)

• Description
  • If jsonpath is specified, this function returns all the keys of a JSON array in a specified path.
  • If jsonpath is not specified, this function returns all the keys in the root path (jsonpath='$').
• Data type of the input value: JSON.
  You can use one of the following methods to build JSON data:

  • Directly use JSON data. Example: json '{"a": 1, "b": {"c": 30}}'.
  • Use the CAST function to explicitly convert a string to JSON data. Example: CAST('{"a": 1, "b": {"c": 30}}' AS json).
• Data type of the return value: JSON ARRAY.
• Examples:
  • This function returns all the keys in the $.b path. Sample statement:

    SELECT json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json),'$.b');

    The following information is returned:

    +-----------------------------------------------------------+
    | json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json),'$.b') |
    +-----------------------------------------------------------+
    | ["c"]                                                     |
    +-----------------------------------------------------------+

  • This function returns all the keys in the root path. Sample statement:

    SELECT JSON_KEYS(json '{"a": 1, "b": {"c": 30}}');

    The following information is returned:

    +--------------------------------------------+
    | JSON_KEYS(json '{"a": 1, "b": {"c": 30}}') |
    +--------------------------------------------+
    | ["a","b"]                                  |
    +--------------------------------------------+

JSON_UNQUOTE

json_unquote(json_value)

• Description: This function unquotes the value specified by json_value, escapes specific characters in the value, and then returns the processing result.

  AnalyticDB for MySQL does not determine the validity of the json_value value. The json_value value is processed based on the preceding logic regardless of whether it conforms to the JSON syntax.

  The following table describes the escape characters.

  Before escape	After escape
  \"	Double quotation marks (")
  \b	Backspace key
  \f	Form feed
  \n	Line feed
  \r	Carriage return
  \t	Tab key
  \\	Backslash (\)
  \uXXXX	UTF-8 character

• Data type of the input value: STRING or JSON.
• Data type of the return value: VARCHAR.
• Examples:
  • Sample statement:

    SELECT json_unquote('"abc"');

    The following information is returned:

    +-----------------------+
    | json_unquote('"abc"') |
    +-----------------------+
    | abc             |
    +-----------------------+

  • Sample statement:

    SELECT json_unquote('"\\t\\u0032"');

    The following information is returned:

    +------------------------------+
    | json_unquote('"\\t\\u0032"') |
    +------------------------------+
    |    2                         |
    +------------------------------+

JSON_CONTAINS

json_contains(target, candidate[, path])

• Description:
  • If path is specified, this function determines whether a given candidate exists in a specified path within a JSON document. A value of 1 indicates true, and a value of 0 indicates false.
  • If path is not specified, this function determines whether a given candidate is contained within a JSON document. A value of 1 indicates true, and a value of 0 indicates false.
  The following rules apply:

  • If both target and candidate are of a primitive type (NUMBER, BOOLEAN, STRING, or NULL) and these two parameters are set to the same value, the candidate is considered to be contained in the JSON document.
  • If both target and candidate are of the JSON ARRAY type and all elements of candidate are contained in an element of target, the candidate is considered to be contained in the JSON document.
  • If target is of the ARRAY type while candidate is not and the value of candidate is contained in an element of target, the candidate is considered to be contained in the JSON document.
  • If both target and candidate are of the JSON OBJECT type, each key of candidate is contained in the key of target, and each key value of candidate is contained in the corresponding key value of target, the candidate is considered to be contained in the JSON document.
• Data types of the input values: JSON for target and candidate; JSONPATH for path.
• Data type of the return value: BOOLEAN.
• Examples:
  • Sample statement:

    select json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '1', '$.a') as result;

    The following information is returned:

    +--------+
    | result |
    +--------+
    |      1 |
    +--------+

  • Sample statement:

    select json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '1', '$.b') as result;

    The following information is returned:

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+

  • Sample statement:

    select json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '{"d": 4}') as result;

    The following information is returned:

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+

JSON_CONTAINS_PATH

json_contains_path(json, one_or_all, path[, path ...])

• Description: This function determines whether a specified path exists in the given JSON document.
  • If one_or_all is set to 'one' and the JSON document contains one of the specified paths, 1 is returned. Otherwise, 0 is returned.
  • If one_or_all is set to 'all' and the JSON document contains all the specified paths, 1 is returned. Otherwise, 0 is returned.
• Data types of the input values: JSON for json; VARCHAR for one_or_all (case-insensitive 'one' or 'all'); JSONPATH for path.
• Data type of the return value: BOOLEAN.
• Examples:
  • Sample statement:

    select json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e') as result;

    The following information is returned:

    +--------+
    | result |
    +--------+
    |      1 |
    +--------+

  • Sample statement:

    select json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e') as result;

    The following information is returned:

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+