# `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](/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`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-to-string.md). To expand an array back into rows, use [`UNNEST`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/listagg.md)
- [JSON_ARRAYAGG](/docs/data/data-cloud-query-guide/references/dc-sql-reference/json-arrayagg.md)
- [ARRAY_TO_STRING](/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-to-string.md)
- [UNNEST](/docs/data/data-cloud-query-guide/references/dc-sql-reference/unnest.md)
- [array data type](/docs/data/data-cloud-query-guide/references/dc-sql-reference/array.md)
- [Aggregate Functions](/docs/data/data-cloud-query-guide/references/dc-sql-reference/aggregate.md)