---
schema_version: "1"
id: 4dc0082a6aa5413b8aacf92a719c970f6dd54ac6cee565c5a418e6a39952d5fc
title: JSON_ARRAY
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/scalar-func/json-func/json-array.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/json-array.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 3491aff763f6112d265e011edd77c38baeacd5f498a25a69b4596abe9cce186d
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# `JSON_ARRAY`

**Applies to:** ✅ Data 360 SQL ✅ Tableau Hyper API

Builds a [JSON](https://developer.salesforce.com/docs/data/data-cloud-query-guide/datatype/json.md) array from a list of values.

## Syntax

```sql
json_array([<value> [FORMAT JSON] [, ...]] [{NULL | ABSENT} ON NULL])
```

## Arguments

### Required

- `<value>`: A value to include in the array. Provide zero or more values separated by commas. Values can be literals, column references, expressions, SQL arrays, or `json`-typed values.

### Optional

- `FORMAT JSON`: Applied to a string argument, treats the string as a pre-formatted JSON document and embeds it without escaping. Invalid JSON raises an error.
- `NULL ON NULL` / `ABSENT ON NULL`: Controls how `NULL` values are handled. `ABSENT ON NULL` (the default) skips `NULL` values. `NULL ON NULL` includes them as JSON `null`.

## Returns

Returns a `json` array. String values are escaped and become JSON strings. `json`-typed values and values marked with `FORMAT JSON` are embedded directly. SQL arrays become nested JSON arrays.

## Considerations

- By default, `NULL` values are skipped. Use `NULL ON NULL` to include them.
- A string argument is escaped into a JSON string unless it's cast to `json` or marked with `FORMAT JSON`.
- With `FORMAT JSON`, invalid JSON is rejected with an `invalid JSON document` error.
- SQL array arguments are converted to nested JSON arrays.
- The `RETURNING` clause from the SQL-standard isn't supported.
- Some types can't be converted, such as `bytes`.

## Examples

### Build a Simple Array

Combine values of different types into a JSON array.

```sql
SELECT json_array(1, true, 'my"string') AS result;
```

Returns `[1,true,"my\"string"]`.

### Control NULL Handling

By default, `NULL` values are skipped. Use `NULL ON NULL` to keep them.

```sql
SELECT
  json_array(null::int, 1, null::int) AS default_behavior,
  json_array(null::int, 1, null::int NULL ON NULL) AS keep_nulls;
```

Returns `[1]` and `[null,1,null]`.

### Embed Pre-Formatted JSON

Use `FORMAT JSON` or a `::json` cast to treat a string as raw JSON instead of escaping it.

```sql
SELECT
  json_array(1, '{"a": 1}', 3) AS escaped,
  json_array(1, '{"a": 1}' FORMAT JSON, 3) AS embedded1,
  json_array(1, '{"a": 1}'::json, 3) AS embedded2;
```

Returns `[1,"{\"a\": 1}",3]` and `[1,{"a": 1},3]`.

### Nest SQL Arrays

SQL array arguments become nested JSON arrays.

```sql
SELECT json_array('[1, -1, null]'::smallint[], 'test') AS result;
```

Returns `[[1,-1,null],"test"]`.

## Related Documentation

- [JSON Type](https://developer.salesforce.com/docs/data/data-cloud-query-guide/datatype/json.md)
- [`JSON_OBJECT`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/json-object.md) - Build a JSON object from key-value pairs.
- [`JSON_SCALAR`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/json-scalar.md) - Convert a scalar value to JSON.
- [`json`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/json-constructor.md) - Parse a string into a JSON value.