JSON_BUILD_OBJECT

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

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"}} |
+---------------------------------------------------------------------------+