JSON_OBJECT
Descriptionβ
Generate one JSON object containing specified Key-Value pairs. Returns an error when the Key value is NULL or when an odd number of parameters is passed.
Syntaxβ
JSON_OBJECT (<key>, <value>[, <key>, <value>, ...])
Parametersβ
Variable parameters:β
<key>
: String type<value>
: Multiple types, Doris will automatically convert non-JSON type parameters to JSON type through theTO_JSON
function.
Notesβ
- The number of parameters must be even, can be 0 parameters (returns an empty JSON object).
- By convention, the parameter list consists of alternating keys and values.
- Keys are forcibly converted to text according to JSON definition.
- If the passed Key is NULL, returns an exception error.
- Value parameters are converted in a way that can be converted to JSON, must be types supported by
TO_JSON
. - If the passed Value is NULL, the Value in the returned JSON object for that Key-Value pair will be JSON null value.
- If you want to support other types as values, you can use CAST to convert them to JSON/String.
- Doris currently does not deduplicate JSON objects, which means duplicate keys are allowed. However, duplicate keys may cause unexpected results:
- Other systems may drop values corresponding to duplicate keys, or report errors.
- The result returned by
JSON_EXTRACT
is uncertain.
Return Valueβ
JSON
: Returns a JSON object composed of the parameter list.
Examplesβ
-
Case with no parameters
select json_object();
+---------------+
| json_object() |
+---------------+
| {} |
+---------------+ -
Unsupported value types
select json_object('time',curtime());
ERROR 1105 (HY000): errCode = 2, detailMessage = Can not find the compatibility function signature: to_json(TIMEV2(0))
Can be converted to String through cast
select json_object('time', cast(curtime() as string));
+------------------------------------------------+
| json_object('time', cast(curtime() as string)) |
+------------------------------------------------+
| {"time":"17:09:42"} |
+------------------------------------------------+ -
Non-String type keys will be converted to String
SELECT json_object(123, 456);
+-----------------------+
| json_object(123, 456) |
+-----------------------+
| {"123":456} |
+-----------------------+ -
Null cannot be used as key
select json_object(null, 456);
ERROR 1105 (HY000): errCode = 2, detailMessage = json_object key can't be NULL: json_object(NULL, 456)
Null can be used as value
select json_object('key', null);
+--------------------------+
| json_object('key', null) |
+--------------------------+
| {"key":null} |
+--------------------------+ -
JSON strings can be parsed into JSON objects via
JSON_PARSE
before being passed toJSON_OBJECT
select json_object(123, json_parse('{"key": "value"}'));
+--------------------------------------------------+
| json_object(123, json_parse('{"key": "value"}')) |
+--------------------------------------------------+
| {"123":{"key":"value"}} |
+--------------------------------------------------+Otherwise it will be treated as a string
select json_object(123,'{"key": "value"}');
+-------------------------------------+
| json_object(123,'{"key": "value"}') |
+-------------------------------------+
| {"123":"{\"key\": \"value\"}"} |
+-------------------------------------+ -
Types not supported by
TO_JSON
select json_object('key', map('abc', 'efg'));
ERROR 1105 (HY000): errCode = 2, detailMessage = Can not find the compatibility function signature: to_json(MAP<VARCHAR(3),VARCHAR(3)>)
Can be converted to JSON via CAST statement before passing in:
select json_object('key', cast(map('abc', 'efg') as json));
+-----------------------------------------------------+
| json_object('key', cast(map('abc', 'efg') as json)) |
+-----------------------------------------------------+
| {"key":{"abc":"efg"}} |
+-----------------------------------------------------+ -
Case with duplicate keys
select
json_object('key', 123, 'key', 4556) v1
, jsonb_extract(json_object('key', 123, 'key', 4556), '$.key') v2
, jsonb_extract(json_object('key', 123, 'key', 4556), '$.*') v3;+------------------------+------+------------+
| v1 | v2 | v3 |
+------------------------+------+------------+
| {"key":123,"key":4556} | 123 | [123,4556] |
+------------------------+------+------------+