JSON_BUILD_OBJECT
Converts a comma-separated argument list to a JSON object.
The argument list consists of alternating keys and values.
Syntax
JSON_BUILD_OBJECT([key1,value1 [:> data_type] [,...]]])
Arguments
key, value
: comma separated key/value pairs. A value can also be typecast to aRECORD
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. Thevalue
arguments are converted according to TO_JSON.A key argument must not be a
NULL
value.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 MPSQL array or aROW()
function without typecasting).JSON_BUILD_OBJECT()
also supports record data types for values in the argument list. Record arguments can be nested insideJSON_BUILD_OBJECT()
.
Examples
The table below was used for all the JSON_BUILD_OBJECT
function examples:
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:
SELECT JSON_BUILD_OBJECT() AS Output; **** +--------+ | Output | +--------+ | {} | +--------+
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.
SELECT JSON_BUILD_OBJECT('id', 1, 'letter', NULL) AS Output; **** +-----------------------+ | Output | +-----------------------+ | {"id":1,"letter":null}| +-----------------------+
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 | +------+------+----------+----------+
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()
:
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:
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()
:
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
:
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
:
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"}} | +---------------------------------------------------------------------------+