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
Notice
ORDER BY
is not supported for the return value of the JSON_EXTRACT function. This feature
is the same as JSON columns.
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 Ifjsonpath
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='$'
).
- If
- 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)
.
- Directly use JSON data. Example:
- 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"] | +--------------------------------------------+
- This function returns all the keys in the