# JSON\_BUILD\_ARRAY

Evaluates a (possibly empty) list of values and returns a JSON array containing those values.

## 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](https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/to-json.md).
* `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());

```

```output

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

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

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

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

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

+-------------------------------------------+
| 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()));

```

```output

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

```

***

Modified at: January 8, 2025

Source: [/cloud/reference/sql-reference/json-functions/json-build-array/](https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-build-array/)

(An index of the documentation is available at /llms.txt)