---
schema_version: "1"
id: c803328b6d3f246f255b50e1be80bee497f2d6834a5f3f7265e5a0576f96497b
title: ARRAY_AGG
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/array-agg.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-agg.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: cced3b19d08f548fb56b0dcc8609fd90f1feda10e1be0d1189ef5e974c519ad2
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# `ARRAY_AGG`

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

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

## Syntax

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

Use with `GROUP BY` to build one array per group:

```sql
SELECT <group_columns>,
       array_agg(<value> ORDER BY <sort_expression>)
FROM <table>
GROUP BY <group_columns>;
```

## Arguments

### Required

- `<value>`: The expression collected into the array. Its result type becomes the element type of the returned 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`.
- `NULL ON NULL`: Collects `NULL` inputs as `NULL` array elements. This is the default.
- `ABSENT ON NULL`: Skips `NULL` inputs so they don't appear in the array.

## Returns

An array whose element type matches `<value>`.

**Null handling:**

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

## Considerations

- `array_agg` keeps `NULL`s by default (`NULL ON NULL`).
- The [`FILTER` clause](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/filter-clause.md) isn't supported for `array_agg`.
- When you combine `DISTINCT` with `ORDER BY`, the sort expression (and its collation) must match the aggregated value.
- If no `ORDER BY` is specified, the resulting array has an implementation-defined, non-deterministic order.
- `array_agg` with `ORDER BY` isn't supported in combination with `ROLLUP`, `CUBE`, or `GROUPING SETS`.
- To turn the resulting array into a delimited string, use [`ARRAY_TO_STRING`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/scalar-func/array-func/array-to-string.md). To expand an array back into rows, use [`UNNEST`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/set-returning-func/unnest.md).

## Examples

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

Order the elements by `timestamp` and skip `NULL` events.

```sql
SELECT array_agg(event ORDER BY timestamp ABSENT ON NULL)
FROM (VALUES ('completed', 2), (NULL, 3), ('started', 1)) AS events(event, timestamp);
```

Returns `["started","completed"]`.

### Keep NULLs (default behavior)

Without `ABSENT ON NULL`, `NULL` inputs are collected as `NULL` elements.

```sql
SELECT array_agg(v ORDER BY k)
FROM (VALUES (1, 1), (NULL, 2), (3, 3)) AS t(v, k);
```

Returns `[1,NULL,3]`.

### One array per group

Build a sorted array of product names for each category.

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

## Related Documentation

- [LISTAGG](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/listagg.md)
- [JSON_ARRAYAGG](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/json-arrayagg.md)
- [ARRAY_TO_STRING](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/scalar-func/array-func/array-to-string.md)
- [UNNEST](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/set-returning-func/unnest.md)
- [array data type](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/datatype/array.md)
- [Aggregate Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/aggregate.md)