JSON_BUILD_OBJECT

Syntax

SQL

JSON_BUILD_OBJECT([key1,value1 [:> data_type] [,...]]])

Arguments

key, value: comma separated key/value pairs. A value can also be typecast to a RECORD data type.

Return Type

A JSON object.

Remarks

The argument list can be an empty list. Running the SELECT JSON_BUILD_OBJECT( ); command returns {}.
The key arguments are converted to strings. The value arguments are converted according to TO_JSON.
If an odd number of arguments is specified, JSON_BUILD_OBJECT() returns an error.
If the same key is specified a multiple number of times with different values, only the last value is applied for the corresponding key value pair, while building the JSON object.
JSON_BUILD_OBJECT() does not support the following data types: BIT, SET, ENUM, and non-scalar data types (for example, an PSQL array or a ROW() function without typecasting).
JSON_BUILD_OBJECT() also supports record data types for values in the argument list. Record arguments can be nested inside JSON_BUILD_OBJECT().

Examples

The table below was used for all the JSON_BUILD_OBJECT function examples:

SQL

CREATE TABLE j_alphabet 
(id varchar(6), letter varchar(25), lang varchar(25), position int);

INSERT INTO j_alphabet VALUES('grk02','beta', 'Greek', '2'), 
('grk01','alpha', 'Greek', '1'),('grk19','tau', 'Greek', '19'), 
('grk04','delta', 'Greek', '4'),('cop10','yota', 'Coptic', '10'),
('cop01','alpha', 'Coptic', '1'),('cop02','beta', 'Coptic', '2'),
('grk03','gamma', 'Greek', '3');

The following examples show how to use the JSON_BUILD_OBJECT function:

SQL

SELECT JSON_BUILD_OBJECT() AS Output;

+--------+
| Output |
+--------+
| {}     |
+--------+

SQL

SELECT JSON_BUILD_OBJECT(letter, position) AS Output
FROM j_alphabet;

+-------------+
| Output      |
+-------------+
| {"yota":10} |
| {"alpha":1} |
| {"beta":2}  |
| {"beta":2}  |
| {"alpha":1} |
| {"tau":19}  |
| {"delta":4} |
| {"gamma":3} |
+-------------+

The following example shows that NULL is a valid argument for a value.

SQL

SELECT JSON_BUILD_OBJECT('id', 1, 'letter', NULL) AS Output;

+-----------------------+
| Output                |
+-----------------------+
| {"id":1,"letter":null}|
+-----------------------+

SQL

SELECT * FROM j_alphabet;

+-------+--------+-------+----------+
| ID    | letter | lang  | position |
+-------+--------+-------+----------+
| grk02 | beta   | Greek |  2       |
| grk01 | alpha  | Greek |  1       |
| grk19 | tau    | Greek | 19       |
| grk04 | delta  | Greek |  4       |
| grk03 | gamma  | Greek |  3       |
| cop10 | yota   | Coptic| 10       |
| cop01 | alpha  | Coptic|  1       |
| cop02 | beta   | Coptic|  2       |
+------+------+----------+----------+

SQL

SELECT JSON_BUILD_OBJECT(ID, JSON_BUILD_OBJECT(letter, lang)) AS Output
FROM j_alphabet;

+-----------------------------+
| Output                      |
+-----------------------------+
| {"cop10":{"yota":"Coptic"}} |
| {"cop01":{"alpha":"Coptic"}}|
| {"cop02":{"beta":"Coptic"}} |
| {"grk02":{"beta":"Greek"}}  |
| {"grk01":{"alpha":"Greek"}} |
| {"grk19":{"tau":"Greek"}}.  |
| {"grk04":{"delta":"Greek"}} |
| {"grk03":{"gamma":"Greek"}} |
+-----------------------------+

The following example shows how to use nested JSON_BUILD_OBJECT():

SQL

SELECT JSON_BUILD_OBJECT(ID, JSON_BUILD_OBJECT(letter, lang)) AS Output
FROM j_alphabet;

+-----------------------------+
| Output                      |
+-----------------------------+
| {"cop10":{"yota":"Coptic"}} |
| {"cop01":{"alpha":"Coptic"}}|
| {"cop02":{"beta":"Coptic"}} |
| {"grk02":{"beta":"Greek"}}  |
| {"grk01":{"alpha":"Greek"}} |
| {"grk19":{"tau":"Greek"}}   |
| {"grk04":{"delta":"Greek"}} |
| {"grk03":{"gamma":"Greek"}} |
+-----------------------------+

The following example shows how to typecast JSON_BUILD_OBJECT() values:

SQL

SELECT JSON_BUILD_OBJECT(id, JSON_BUILD_OBJECT('letter', letter:>CHAR)) AS Output
FROM j_alphabet;

+--------------------------+
| Output                   |
+--------------------------+
| {"cop10":{"letter":"y"}} |
| {"cop01":{"letter":"a"}} |
| {"cop02":{"letter":"b"}} |
| {"grk02":{"letter":"b"}} |
| {"grk01":{"letter":"a"}} |
| {"grk19":{"letter":"t"}} |
| {"grk04":{"letter":"d"}} |
| {"grk03":{"letter":"g"}} |
+--------------------------+

A RECORD can be used inside the JSON_BUILD_OBJECT() function instead of the :> operator to typecast the output values. The following example shows how to use records with JSON_BUILD_OBJECT():

SQL

SELECT JSON_BUILD_OBJECT(ID, ROW(letter, position):>RECORD(letter VARCHAR(10), 
position INT)) AS Output FROM j_alphabet;

+-------------------------------------------+
| Output                                    |
+-------------------------------------------+
| {"cop10":{"letter":"yota","position":10}} |
| {"cop01":{"letter":"alpha","position":1}} |
| {"cop02":{"letter":"beta","position":2}}  |
| {"grk02":{"letter":"beta","position":2}}  |
| {"grk01":{"letter":"alpha","position":1}} |
| {"grk19":{"letter":"tau","position":19}}  |
| {"grk04":{"letter":"delta","position":4}} |
| {"grk03":{"letter":"gamma","position":3}} |
+-------------------------------------------+

You can also format the JSON output using JSON_PRETTY:

SQL

SELECT JSON_PRETTY(JSON_BUILD_OBJECT(ID, ROW(letter, position):>RECORD(letter VARCHAR(10), position INT))) AS Output
FROM j_alphabet;

+--------------------------------------------------------+
| Output                                                 |
+--------------------------------------------------------+
{
  "cop10": {
    "letter": "yota",
    "position": 10
  }
}
+--------------------------------------------------------+
{
  "cop01": {
    "letter": "alpha",
    "position": 1
  }
}
+--------------------------------------------------------+
{
  "cop02": {
    "letter": "beta",
    "position": 2
  }
}
+--------------------------------------------------------+
{
  "grk02": {
    "letter": "beta",
    "position": 2
  }
}
+--------------------------------------------------------+
{
  "grk01": {
    "letter": "alpha",
    "position": 1
  }
}
+--------------------------------------------------------+
  "grk19": {
    "letter": "tau",
    "position": 19
  }
}
+--------------------------------------------------------+
{
  "grk04": {
    "letter": "delta",
    "position": 4
  }
}
+--------------------------------------------------------+
{
  "grk03": {
    "letter": "gamma",
    "position": 3
  }
}
+--------------------------------------------------------+

Here's another example of using records inside JSON_BUILD_OBJECT:

SQL

SELECT JSON_BUILD_OBJECT(ID, ROW(letter,ROW(letter, 
position)):>RECORD(letter_Code VARCHAR(3), Details RECORD(letter VARCHAR(10), 
position INT))) AS Output FROM j_alphabet;

+---------------------------------------------------------------------------+
| Output                                                                    |
+---------------------------------------------------------------------------+
| {"cop10":{"Details":{"letter":"yota","position":10},"letter_Code":"yot"}} |
| {"cop01":{"Details":{"letter":"alpha","position":1},"letter_Code":"alp"}} |
| {"cop02":{"Details":{"letter":"beta","position":2},"letter_Code":"bet"}}  |
| {"grk02":{"Details":{"letter":"beta","position":2},"letter_Code":"bet"}}  |
| {"grk01":{"Details":{"letter":"alpha","position":1},"letter_Code":"alp"}} |
| {"grk19":{"Details":{"letter":"tau","position":19},"letter_Code":"tau"}}  |
| {"grk04":{"Details":{"letter":"delta","position":4},"letter_Code":"del"}} |
| {"grk03":{"Details":{"letter":"gamma","position":3},"letter_Code":"gam"}} |
+---------------------------------------------------------------------------+