Collection functions

SQL Function Description

ARRAY[int]

Returns the element at position int in array. The index starts from 1.

For example, assume that you have a field called inventory in your records with the array:

["shampoo", "conditioner", "body wash"]

To access the conditioner element in this list, use ARRAY[2].

ELEMENT(array)

Returns the sole element of array (whose cardinality should be one); returns NULL if array is empty. Throws an exception if array has more than one element.

CARDINALITY(array)

CARDINALITY(map)

Returns the number of entries in array or map.

map[value]

Returns the value specified by key value in map.

For example, assume that you have a field called zoo in your records with the map:

{"mammals":"tiger", "fish":"goldfish"}

Then, zoo['mammals'] would return tiger.

ARRAY_AGG(expression)

Accepts a set of values and returns an array in which each value in the set is assigned to an element of the array. This function can be used to aggregate the elements of the many-side of a 1:n join. It can be used with scalar values as well as ROW values. In contrast to JSON_ARRAYAGG(), this function emits a typed array.

Unlike other aggregate functions, ARRAY_AGG() doesn’t remove null values. If required, the FILTER clause can be used for removing null values before the aggregation, including NULL rows, which are rows that contain only NULL attributes as these are considered to be NULL themselves. If all values are removed, ARRAY_AGG() will return NULL, rather than an empty array.

Example 1: Aggregating scalar values:

SELECT
  po.id,
  po.customer,
  array_agg(ol.item) AS items
  FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id
  GROUP BY po.id, po.customer

Example result: 1, 'Bob', ['hammer', 'nails']

Example 2: Aggregating ROW values:

SELECT
  po.id,
  po.customer,
  array_agg(row(ol.id, ol.item)) AS items
  FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id
  GROUP BY po.id, po.customer

Example result: 1, 'Bob', [ { 11, 'hammer' }, { 12, 'nails'} ]

Example 3: Aggregating ROW values, omitting any NULL rows:

SELECT
  po.id,
  po.customer,
  array_agg(row(ol.id, ol.item)) FILTER (WHERE ol.id IS NOT NULL) AS items
  FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id
  GROUP BY po.id, po.customer`

Example result: 1, 'Bob', null