Db2: How to create an array from a query

Db2 ordinary arrays can be constructed and populated from an SQL query. Let’s see how to do it.

Creating an array from a row

As you probably know, you can create an array by typing its literal values:

SET xmp_array = [1, 2, 3]

Creating an array from the columns returned by a query is similar:

SET finance_january = [
    SELECT income, outcome
        FROM finance_by_month
        WHERE year = 2021 AND month = 1

Each column returned by the SELECT will be an array item. The query must return only one row. Column names or aliases are not important.

Creating an array from a column

But what if we want to run a 1-column SELECT, and put all the rows into an array? In that case, we can use the ARRAY_AGG() function:

SET sales_2021 = (
        FROM sales
        WHERE year = 2021
        GROUP BY year

ARRAY_AGG() is an aggregate function that returns an ordinary (non-associative) array. Our example is trivial, but instead of a column you may pass it an expression. ORDER BY is optional, and only useful if the order of elements is relevant. ASC and DESC are supported.

ARRAY_AGG() is similar to LIST_AGG(), which returns a list as a string, like a CSV row.


Db2 documentation: