Salesforce Docs Portal

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

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 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 conversion rules.

Examples

Aggregate values in a specified order, dropping NULLs

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

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.

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.

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

Related Documentation