JSON_OBJECT

Description

Generate a json object containing the specified Key-Value, an exception error is returned when Key is NULL or the number of parameters are odd.

Syntax

JSON_OBJECT (<key>, <value>[,<key>, <value>, ...])

Parameters

Parameter	Description
<key>	The Key value in the Key-Value of the generated json object.
<value>	The Value value in the Key-Value of the generated json object.

Usage Notes

• By convention, the argument list consists of alternating keys and values.
• Key arguments are coerced to text.
• Value arguments are converted as per can convert to json, now we support array/struct/map/json as value.

Return Values

Return a json object. Special cases are as follows:

• If no parameters are passed, return an empty json object.
• If the number of parameters passed is odd, return an exception error.
• If the passed Key is NULL, return an exception error.
• If the passed Value is NULL, the Value value of the Key-Value pair in the returned json object is NULL.

Examples

select json_object();

+---------------+
| json_object() |
+---------------+
| {}            |
+---------------+

select json_object('time',curtime());

+--------------------------------+
| json_object('time', curtime()) |
+--------------------------------+
| {"time": "10:49:18"}           |
+--------------------------------+

SELECT json_object('id', 87, 'name', 'carrot');

+-----------------------------------------+
| json_object('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------+

select json_object('username',null);

+---------------------------------+
| json_object('username', 'NULL') |
+---------------------------------+
| {"username": NULL}              |
+---------------------------------+

select json_object(null,null);

ERROR 1105 (HY000): errCode = 2, detailMessage = json_object key can't be NULL: json_object(NULL)

-- support array as object value
SELECT json_object('id', 1, 'level', array('"aaa"','"bbb"'));

+------------------------------------------------------------------------------------------------------+
| json_object('id', cast(1 as VARCHAR(65533)), 'level', cast(array('"aaa"', '"bbb"') as JSON), '6267') |
+------------------------------------------------------------------------------------------------------+
| {"id":1,"level":["\"aaa\"","\"bbb\""]}                                                               |
+------------------------------------------------------------------------------------------------------+

-- support map as object value
SELECT json_object('id', 1, 'level', map('a', 'b', 'c', 'd'));

+------------------------------------------------------------------------------------------------------+
| json_object('id', cast(1 as VARCHAR(65533)), 'level', cast(map('a', 'b', 'c', 'd') as JSON), '6267') |
+------------------------------------------------------------------------------------------------------+
| {"id":1,"level":{"a":"b","c":"d"}}                                                                   |
+------------------------------------------------------------------------------------------------------+

-- support struct as object value
SELECT json_object('id', 1, 'level', named_struct('name', 'a', 'age', 1));

+------------------------------------------------------------------------------------------------------------------+
| json_object('id', cast(1 as VARCHAR(65533)), 'level', cast(named_struct('name', 'a', 'age', 1) as JSON), '6267') |
+------------------------------------------------------------------------------------------------------------------+
| {"id":1,"level":{"name":"a","age":1}}                                                                            |
+------------------------------------------------------------------------------------------------------------------+

-- support json as object value
SELECT json_object('id', 1, 'level', cast('{\"a\":\"b\"}' as JSON));

+------------------------------------------------------------------------------------------+
| json_object('id', cast(1 as VARCHAR(65533)), 'level', cast('{"a":"b"}' as JSON), '6267') |
+------------------------------------------------------------------------------------------+
| {"id":1,"level":{"a":"b"}}                                                               |
+------------------------------------------------------------------------------------------+