DB Software v7.8

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

  • 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 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 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"}} |
+--------------------------------------------------------------------------+
In this section: