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

JSON_EXTRACT

json_extract(json, jsonpath)
  • Description: This function returns the value specified by jsonpath from json.
  • Return value type: JSON.
  • Example: 
    select json_extract('[10, 20, [30, 40]]', '$.1');
  +------------------------------------------------+
  |                      20                        |
    select json_extract('[10, 20, [30, 40]]', '$[1]');
  +------------------------------------------------+
  |                      20                        |

JSON _ARRAY_CONTAINS

json_array_contains(json, value)
  • Description: This function determines whether json contains the value specified by value.
  • Return value type: BOOLEAN.
  • Example: 
    select json_array_contains('[1, 2, 3]', 2);
  +-----------------------------------------+
  |                    1                    |

JSON_SIZE

json_size(json, jsonpath)
  • Description: This function returns the size of json.
  • Return value type: BIGINT.
  • Example: 
    select json_size('{"x":{"a":1, "b": 2}}', '$.x') as result;
  +---------------------------------------------------------+
  |                            2                            |
    select json_size('{"x": {"a": 1, "b": 2}}', '$.x.a') as result; 
  +------------------------------------------------------+
  |                          0                           |

JSON_ARRAY_LENGTH

json_array_length(json)
  • Description: This function returns the length of the json array.
  • Return value type: BIGINT.
  • Example: 
    select json_array_length('[1, 2, 3]')
  +-----------------------------------+
  |                 3                 |

JSON_KEYS

json_keys(json, jsonpath)
  • Description: This function obtains all the key values of json in a specified path.
  • Return value type: JSON ARRAY.
  • Example: 
    select json_keys(cast('{"a": 1, "b": {"c": 30}}' as json),'$.b')
  +------------------------------------------------------------+
  |                         ["c"]                              |