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"]                                  |
+--------------------------------------------+