Skip to main content

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 the TO_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:
    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] |
    +------------------------+------+------------+