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>, ...])

JSON_OBJECT(*)

Parameters

Variable parameters:

• <key>: String type

• <value>: Multiple types, Doris will automatically convert non-JSON type parameters to JSON type through the TO_JSON function.

• *: When invoked with an asterisk (wildcard), the OBJECT value is constructed from the specified data using the attribute names as keys and the associated values as values.

  When you pass a wildcard to the function, you can qualify the wildcard with the name or alias for the table. For example, to pass in all of the columns from the table named mytable, specify the following:

  (mytable.*)

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:
  1. Other systems may drop values corresponding to duplicate keys, or report errors.
  2. The result returned by JSON_EXTRACT is uncertain.

Return Value

JSON: Returns a JSON object composed of the parameter list.

Examples

1. Case with no parameters

   select json_object();

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

2. 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"}                            |
   +------------------------------------------------+

3. Non-String type keys will be converted to String

   SELECT json_object(123, 456);

   +-----------------------+
   | json_object(123, 456) |
   +-----------------------+
   | {"123":456}           |
   +-----------------------+

4. 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}             |
   +--------------------------+

5. JSON strings can be parsed into JSON objects via JSON_PARSE before being passed to JSON_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\"}"}      |
   +-------------------------------------+

6. 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"}}                               |
   +-----------------------------------------------------+

7. 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] |
   +------------------------+------+------------+