JSON_ARRAY
Descriptionβ
Generate a JSON array containing the specified elements. Returns an empty array when no parameters are passed.
Syntaxβ
JSON_ARRAY([<expression>, ...])
Parametersβ
Variable parameters:β
<expression>
: Elements to be included in the JSON array. Can be single or multiple values of different types, including NULL.
Return Valueβ
JSON
: Returns a JSON array composed of the parameter list.
Usage Notesβ
- JSON_ARRAY implementation converts different types of parameters to JSON values by implicitly calling the
TO_JSON
function, so parameters must be types supported byTO_JSON
- NULL will be converted to JSON null. If you don't want to retain null values in the array, you can use the function
JSON_ARRAY_IGNORE_NULL
. - If the parameter type is not supported by
TO_JSON
, you will get an error. You can first convert that parameter to String type, for example:select JSON_ARRAY(CAST(NOW() as String));
The NOW() function returns a DateTime type, which needs to be converted to String type using the CAST function
- If the parameter is a JSON string and you want to add it to the array as a JSON object, you should explicitly call the
JSON_PARSE
function to parse it as a JSON object:select JSON_ARRAY(JSON_PARSE('{"key": "value"}'));
Examplesβ
- Regular parameters
select json_array() as empty_array, json_array(1) v1, json_array(1, 'abc') v2;
+-------------+------+-----------+
| empty_array | v1 | v2 |
+-------------+------+-----------+
| [] | [1] | [1,"abc"] |
+-------------+------+-----------+ - NULL parameters
select json_array(null) v1, json_array(1, null, 'I am a string') v2;
+--------+--------------------------+
| v1 | v2 |
+--------+--------------------------+
| [null] | [1,null,"I am a string"] |
+--------+--------------------------+ - Unsupported parameter types
select json_array('item1', map(123, 'abc'));
ERROR 1105 (HY000): errCode = 2, detailMessage = Can not find the compatibility function signature: to_json(MAP<TINYINT,VARCHAR(3)>)
- Map type parameters can be explicitly converted to JSON
select json_array(1, cast(map('key', 'value') as json));
+--------------------------------------------------+
| json_array(1, cast(map('key', 'value') as json)) |
+--------------------------------------------------+
| [1,{"key":"value"}] |
+--------------------------------------------------+ - The JSON string will be added to the array in the form of a string
select json_array('{"key1": "value", "key2": [1, "I am a string", 3]}');
+------------------------------------------------------------------+
| json_array('{"key1": "value", "key2": [1, "I am a string", 3]}') |
+------------------------------------------------------------------+
| ["{\"key1\": \"value\", \"key2\": [1, \"I am a string\", 3]}"] |
+------------------------------------------------------------------+ - A JSON string can be parsed using
json_parse
and then passed tojson_array
select json_array(json_parse('{"key1": "value", "key2": [1, "I am a string", 3]}'));
+------------------------------------------------------------------------------+
| json_array(json_parse('{"key1": "value", "key2": [1, "I am a string", 3]}')) |
+------------------------------------------------------------------------------+
| [{"key1":"value","key2":[1,"I am a string",3]}] |
+------------------------------------------------------------------------------+