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_UNQUOTE: unquotes the value specified by
json_value
, escapes specific characters injson_value
, and then returns the processing result. - JSON_CONTAINS: determines whether a given
candidate
is contained within a JSON document or whether thecandidate
exists in a specified path within the JSON document. - JSON_CONTAINS_PATH: determines whether a specified path exists in the given JSON document.
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 information 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 information is returned:+--------------------------------+ | json_array_length('[1, 2, 3]') | +--------------------------------+ | 3 | +--------------------------------+
JSON _EXTRACT
- Similar to JSON columns, the return values of the JSON_EXTRACT function do not support
ORDER BY
. - When the JSON_EXTRACT function is used together with the JSON_UNQUOTE function, you must invoke CAST AS VARCHAR to convert the return values of JSON_EXTRACT to the VARCHAR type before they can be used as input parameters of JSON_UNQUOTE.
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 information 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 information 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 information 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 information 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 information is returned:+--------------------------------------------+ | JSON_KEYS(json '{"a": 1, "b": {"c": 30}}') | +--------------------------------------------+ | ["a","b"] | +--------------------------------------------+
- This function returns all the keys in the
JSON_UNQUOTE
json_unquote(json_value)
For information about how to view the version of an AnalyticDB for MySQL cluster, see How can I view the version of an AnalyticDB for MySQL cluster?
To update the version of a cluster, Submit a ticket.
- Description: This function unquotes the value specified by
json_value
, escapes specific characters in the value, and then returns the processing result.AnalyticDB for MySQL does not determine the validity of the
json_value
value. Thejson_value
value is processed based on the preceding logic regardless of whether it conforms to the JSON syntax.The following table describes the escape characters.Before escape After escape \"
Double quotation marks ( "
)\b
Backspace key \f
Form feed \n
Line feed \r
Carriage return \t
Tab key \\
Backslash ( \
)\uXXXX
UTF-8 character - Data type of the input value: VARCHAR.
- Data type of the return value: VARCHAR.
- Examples:
- Sample statement:
SELECT json_unquote('"abc"');
The following information is returned:+-----------------------+ | json_unquote('"abc"') | +-----------------------+ | abc | +-----------------------+
- Sample statement:
SELECT json_unquote('"\\t\\u0032"');
The following information is returned:+------------------------------+ | json_unquote('"\\t\\u0032"') | +------------------------------+ | 2 | +------------------------------+
- Sample statement:
JSON_CONTAINS
json_contains(target, candidate[, path])
For information about how to view the version of an AnalyticDB for MySQL cluster, see How can I view the version of an AnalyticDB for MySQL cluster?
To update the version of a cluster, Submit a ticket.
- Description:
- If
path
is specified, this function determines whether a givencandidate
exists in a specified path within a JSON document. A value of 1 indicates true, and a value of 0 indicates false. - If
path
is not specified, this function determines whether a givencandidate
is contained within a JSON document. A value of 1 indicates true, and a value of 0 indicates false.
The following rules apply:- If both
target
andcandidate
are of a primitive type (NUMBER, BOOLEAN, STRING, or NULL) and these two parameters are set to the same value, the candidate is considered to be contained in the JSON document. - If both
target
andcandidate
are of the JSON ARRAY type and all elements of candidate are contained in an element of target, the candidate is considered to be contained in the JSON document. - If
target
is of the ARRAY type whilecandidate
is not and the value of candidate is contained in an element of target, the candidate is considered to be contained in the JSON document. - If both
target
andcandidate
are of the JSON OBJECT type, each key of candidate is contained in the key of target, and each key value of candidate is contained in the corresponding key value of target, the candidate is considered to be contained in the JSON document.
- If
- Data types of the input values: JSON for
target
andcandidate
; JSONPATH forpath
. - Data type of the return value: BOOLEAN.
- Examples:
- Sample statement:
select json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '1', '$.a') as result;
The following information is returned:+--------+ | result | +--------+ | 1 | +--------+
- Sample statement:
select json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '1', '$.b') as result;
The following information is returned:+--------+ | result | +--------+ | 0 | +--------+
- Sample statement:
select json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '{"d": 4}') as result;
The following information is returned:+--------+ | result | +--------+ | 0 | +--------+
- Sample statement:
JSON_CONTAINS_PATH
json_contains_path(json, one_or_all, path[, path ...])
For information about how to view the version of an AnalyticDB for MySQL cluster, see How can I view the version of an AnalyticDB for MySQL cluster?
To update the version of a cluster, Submit a ticket.
- Description: This function determines whether a specified path exists in the given
JSON document.
- If
one_or_all
is set to'one'
and the JSON document contains one of the specified paths, 1 is returned. Otherwise, 0 is returned. - If
one_or_all
is set to'all'
and the JSON document contains all the specified paths, 1 is returned. Otherwise, 0 is returned.
- If
- Data types of the input values: JSON for
json
; VARCHAR forone_or_all
(case-insensitive'one'
or'all'
); JSONPATH forpath
. - Data type of the return value: BOOLEAN.
- Examples:
- Sample statement:
select json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e') as result;
The following information is returned:+--------+ | result | +--------+ | 1 | +--------+
- Sample statement:
select json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e') as result;
The following information is returned:+--------+ | result | +--------+ | 0 | +--------+
- Sample statement: