Salesforce Docs Portal

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

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:

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 NULLs by default (NULL ON NULL).
  • The FILTER clause 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. To expand an array back into rows, use UNNEST.

Examples

Aggregate values in a specified order, dropping NULLs

Order the elements by timestamp and skip NULL events.

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.

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.

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

Related Documentation