---
schema_version: "1"
id: eac2323e2ea815f866def2df1fb46ed83acf5f89bc81fab09c3f4ca453a9fd57
title: JSON_ARRAYAGG
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/aggregate-func/json-arrayagg.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/json-arrayagg.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: ad3c4d59ef57b96e65cba4c49f74b114c7d33f396dffc4ea40c9579b7fbba078
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# `JSON_ARRAYAGG`

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

Aggregates the input values from a group into a JSON array, using an optional, user-supplied order.

## Syntax

```sql
json_arrayagg([DISTINCT] <value> [ORDER BY <sort_expression> [ASC | DESC] [NULLS { FIRST | LAST }] [, ...]] [NULL ON NULL | ABSENT ON NULL])
```

## Arguments

### Required

- `<value>`: The single expression collected into the JSON array.

### Optional

- `DISTINCT`: Keeps only distinct values. When you use `DISTINCT`, the `ORDER BY` expression (including its collation) must match `<value>`.
- `ORDER BY <sort_expression> [, ...]`: Orders the elements within the array (an aggregate-local ordering). Each sort key accepts `ASC` / `DESC` and `NULLS FIRST` / `NULLS LAST`.
- `ABSENT ON NULL`: Skips `NULL` inputs so they don't appear in the array. This is the default.
- `NULL ON NULL`: Keeps `NULL` inputs as JSON `null` elements.

## Returns

A `json` value containing the aggregated array.

**Null handling:**

- With the default `ABSENT ON NULL`, `NULL` inputs are dropped. A group whose values are all `NULL` returns an empty array (`[]`).
- With `NULL ON NULL`, `NULL` inputs are kept as JSON `null` elements.

## Considerations

- The [`FILTER` clause](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/filter-clause.md) isn't supported for `json_arrayagg`.
- If no `ORDER BY` is specified, the resulting array has an implementation-defined, non-deterministic order.
- `json_arrayagg` with `ORDER BY` isn't supported in combination with `ROLLUP`, `CUBE`, or `GROUPING SETS`.
- The `<value>` is serialized to JSON using the [json_scalar](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/scalar-func/json-func/json-scalar.md) conversion rules.

## Examples

### Aggregate values in a specified order, dropping NULLs

Order elements by `id` and drop the `NULL` name (the default behavior).

```sql
SELECT json_arrayagg(name ORDER BY id ABSENT ON NULL)
FROM (VALUES (2, 'Grace'), (1, 'Ada'), (3, NULL)) AS people(id, name);
```

Returns `["Ada","Grace"]`.

### Keep NULLs as JSON null

Use `NULL ON NULL` to retain `NULL` inputs as JSON `null` elements.

```sql
SELECT json_arrayagg(v ORDER BY k NULL ON NULL)
FROM (VALUES (20, 1), (NULL, 2), (30, 3)) AS t(v, k);
```

Returns `[20,null,30]`.

### One JSON array per group

Build a JSON array of product names for each category.

```sql
SELECT category,
       json_arrayagg(product_name ORDER BY product_name)
FROM products
GROUP BY category;
```

## Related Documentation

- [ARRAY_AGG](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-agg.md)
- [LISTAGG](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/listagg.md)
- [JSON_SCALAR](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/scalar-func/json-func/json-scalar.md)
- [JSON Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/scalar-func/json-func/json-funcs.md)
- [Aggregate Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/aggregate.md)