JSON_ARRAY_IGNORE_NULL
Descriptionβ
Generate a JSON array containing the specified elements. Returns an empty array when no parameters are passed.
Syntaxβ
JSON_ARRAY_IGNORE_NULL([<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_IGNORE_NULL 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 ingored. If you DO want to retain null values in the array, you can use the function
JSON_ARRAY
. - 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_IGNORE_NULL(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_IGNORE_NULL(JSON_PARSE('{"key": "value"}'));
Examplesβ
-
Regular parameters
select json_array_ignore_null() as empty_array, json_array_ignore_null(1) v1, json_array_ignore_null(1, 'abc') v2;
+-------------+------+-----------+
| empty_array | v1 | v2 |
+-------------+------+-----------+
| [] | [1] | [1,"abc"] |
+-------------+------+-----------+ -
NULL parameters
select json_array_ignore_null(null) v1, json_array_ignore_null(1, null, 'I am a string') v2;
+------+---------------------+
| v1 | v2 |
+------+---------------------+
| [] | [1,"I am a string"] |
+------+---------------------+ -
Unsupported parameter types
select json_array_ignore_null('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_ignore_null(1, cast(map('key', 'value') as json));
+--------------------------------------------------------------+
| json_array_ignore_null(1, cast(map('key', 'value') as json)) |
+--------------------------------------------------------------+
| [1,{"key":"value"}] |
+--------------------------------------------------------------+ -
JSON strings can be parsed with
json_parse
select json_array_ignore_null(json_parse('{"key1": "value", "key2": [1, "I am a string", 3]}'));
+------------------------------------------------------------------------------------------+
| json_array_ignore_null(json_parse('{"key1": "value", "key2": [1, "I am a string", 3]}')) |
+------------------------------------------------------------------------------------------+
| [{"key1":"value","key2":[1,"I am a string",3]}] |
+------------------------------------------------------------------------------------------+