This topic describes the JSON functions used in AnalyticDB for MySQL.

  • 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 _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 result 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 result 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 result 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 result 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 result 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 result 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 result is returned:
      +--------------------------------------------+
      | JSON_KEYS(json '{"a": 1, "b": {"c": 30}}') |
      +--------------------------------------------+
      | ["a","b"]                                  |
      +--------------------------------------------+