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
fromjson
. - Return value type: JSON.
- Example:
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 byvalue
. - 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"] |