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 following examples show how to use the JSON_BUILD_OBJECT()
function:
SELECT JSON_BUILD_OBJECT('id', 1, 'tag', 'sim') AS Output; **** +----------------------+ | Output | +----------------------+ | {"id":1,"tag":"sim"} | +----------------------+
SELECT JSON_BUILD_OBJECT() AS Output; **** +--------+ | Output | +--------+ | {} | +--------+
The following example shows that NULL
is a valid argument for a value.
SELECT JSON_BUILD_OBJECT('id', 1, 'tag', NULL) AS Output; **** +---------------------+ | Output | +---------------------+ | {"id":1,"tag":null} | +---------------------+
Note
The following examples use the product_quantity
table:
SELECT * FROM product_quantity; **** +------+------+----------+----------+ | ID | Code | Category | Quantity | +------+------+----------+----------+ | 1 | xcn | cat1 | 20 | | 3 | iql | cat1 | 18 | | 2 | thk | cat2 | 15 | | 4 | rwn | cat2 | 25 | +------+------+----------+----------+
SELECT JSON_BUILD_OBJECT(Code, Quantity) AS Output FROM product_quantity; **** +------------+ | Output | +------------+ | {"xcn":20} | | {"iql":18} | | {"thk":15} | | {"rwn":25} | +------------+
The following example shows how to use nested JSON_BUILD_OBJECT()
:
SELECT JSON_BUILD_OBJECT(ID, JSON_BUILD_OBJECT(Code, Quantity)) AS Output FROM product_quantity; **** +------------------+ | Output | +------------------+ | {"1":{"xcn":20}} | | {"3":{"iql":18}} | | {"2":{"thk":15}} | | {"4":{"rwn":25}} | +------------------+
The following example shows how to typecast JSON_BUILD_OBJECT()
values:
SELECT JSON_BUILD_OBJECT(Code, JSON_BUILD_OBJECT('Category', Category:>CHAR)) AS Output FROM product_quantity; **** +--------------------------+ | Output | +--------------------------+ | {"xcn":{"Category":"c"}} | | {"iql":{"Category":"c"}} | | {"thk":{"Category":"c"}} | | {"rwn":{"Category":"c"}} | +--------------------------+
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(Code,Quantity):>RECORD(Code VARCHAR(3), Quantity INT)) AS Output FROM product_quantity; **** +------------------------------------+ | Output | +------------------------------------+ | {"1":{"Code":"xcn","Quantity":20}} | | {"3":{"Code":"iql","Quantity":18}} | | {"2":{"Code":"thk","Quantity":15}} | | {"4":{"Code":"rwn","Quantity":25}} | +------------------------------------+
You can also format the JSON output using JSON_PRETTY
:
SELECT JSON_PRETTY(JSON_BUILD_OBJECT(ID, ROW(Code,Quantity):>RECORD(Code VARCHAR(3), Quantity INT))) AS Output FROM product_quantity; **** +--------------------------------------------------------+ | Output | +--------------------------------------------------------+ | { "1": { "Code": "xcn", "Quantity": 20 } } | | { "2": { "Code": "thk", "Quantity": 15 } } | | { "3": { "Code": "iql", "Quantity": 18 } } | | { "4": { "Code": "rwn", "Quantity": 25 } } | +--------------------------------------------------------+
Here's another example of using records inside JSON_BUILD_OBJECT
:
SELECT JSON_BUILD_OBJECT(ID, ROW(Code,ROW(Category,Quantity)):>RECORD(Product_Code VARCHAR(3), Details RECORD(Category VARCHAR(4), Quantity INT))) AS Output FROM product_quantity; **** +--------------------------------------------------------------------------+ | Output | +--------------------------------------------------------------------------+ | {"1":{"Details":{"Category":"cat1","Quantity":20},"Product_Code":"xcn"}} | | {"3":{"Details":{"Category":"cat1","Quantity":18},"Product_Code":"iql"}} | | {"2":{"Details":{"Category":"cat2","Quantity":15},"Product_Code":"thk"}} | | {"4":{"Details":{"Category":"cat2","Quantity":25},"Product_Code":"rwn"}} | +--------------------------------------------------------------------------+