JSON_BUILD_ARRAY

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