JSON_BUILD_ARRAY

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_ARRAY([val[, val] ...])

Arguments

A possibly empty list of comma-separated values.

Return Type

A JSON array.

Remarks

The argument list can be an empty list.
The arguments are converted according to TO_JSON.
SQL-NULL is a valid argument value and will be converted to JSON-null.
JSON_BUILD_ARRAY does not support the data types: BIT, SET, ENUM.
JSON_BUILD_ARRAY does not support non-scalar data types, except for the RECORD data type.
- A RECORD argument is a valid argument for JSON_BUILD_ARRAY.
- A ROW function requires typecasting to be used with JSON_BUILD_ARRAY.

Examples

The following examples show how to use the JSON_BUILD_ARRAY function.

Use JSON_BUILD_ARRAY to create a JSON array from a list of arguments. Note that NULL is a valid argument value and will be converted to JSON-null.

SQL

SELECT JSON_BUILD_ARRAY(1, "abc", NULL, TRUE, CURTIME());

+---------------------------------------------------+
| JSON_BUILD_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------------+
| [1,"abc",null,1,"18:30:20"]                       |
+---------------------------------------------------+

Use JSON_BUILD_ARRAY with an empty argument list to create an empty JSON array.

SQL

SELECT JSON_BUILD_ARRAY() AS Output;

+--------+
| Output |
+--------+
| []     |
+--------+

Use nested JSON_BUILD_ARRAY functions to create a nested JSON array.

SQL

SELECT JSON_BUILD_ARRAY(JSON_BUILD_ARRAY(1, 2),3,4) AS Output;

+-------------+
| Output      |
+-------------+
| [[1,2],3,4] |
+-------------+

Use JSON_BUILD_ARRAY to convert a row from a table into a JSON array.

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');

SQL

SELECT JSON_BUILD_ARRAY(id, letter, lang, position) AS Output from j_alphabet;

+------------------------------+
| Output                       |
+------------------------------+
| ["grk19","tau","Greek",19]   |
| ["cop02","beta","Coptic",2]  |
| ["grk01","alpha","Greek",1]  |
| ["grk03","gamma","Greek",3]  |
| ["cop10","yota","Coptic",10] |
| ["grk04","delta","Greek",4]  |
| ["cop01","alpha","Coptic",1] |
| ["grk02","beta","Greek",2]   |
+------------------------------+

The following example shows how to typecast JSON_BUILD_ARRAY values.

SQL

SELECT JSON_BUILD_ARRAY(letter, letter:>char) AS Output from j_alphabet;

+---------------+
| Output        |
+---------------+
| ["tau","t"]   |
| ["beta","b"]  |
| ["alpha","a"] |
| ["gamma","g"] |
| ["yota","y"]  |
| ["delta","d"] |
| ["alpha","a"] |
| ["beta","b"]  |
+---------------+

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

SQL

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

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

You can also format the JSON output using JSON_PRETTY:

SQL

SELECT JSON_PRETTY(JSON_BUILD_ARRAY(1, "abc", NULL, TRUE, CURTIME()));

+----------------------------------------------------------------+
| JSON_PRETTY(JSON_BUILD_ARRAY(1, "abc", NULL, TRUE, CURTIME())) |
+----------------------------------------------------------------+
| [
  1,
  "abc",
  null,
  1,
  "16:46:23"
]                    |
+----------------------------------------------------------------+