Postgres table to two-dimensional array

Question

I would like to convert a table with three columns to a two-dimensional array of type integer[][]. There are two columns to indicate each of the two dimensions of the array (x and y in the example) and one integer column to indicate the value.

All possible combinations of x and y are accounted for in the data, though it would be nice if a possible solution could substitute NULL for missing combinations of x and y.

The table looks as such:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
    x VARCHAR,
    y VARCHAR,
    val INT
);

INSERT INTO t1 (x, y, val)
VALUES   ('A', 'A', 1),
         ('A', 'B', 2),
         ('A', 'C', 3),
         ('B', 'A', 4),
         ('B', 'B', 5),
         ('B', 'C', 6),
         ('C', 'A', 7),
         ('C', 'B', 8),
         ('C', 'C', 9);

SELECT * FROM t1

How can I write this query to return a two-dimensional array?

Eg. the result of this specific query should be the following array:

SELECT '{{1,2,3},{4,5,6},{7,8,9}}'::integer[][]

Answer 1

One possibility is to first group by x and use array_agg() to get the inner arrays. Then aggregate again using array_agg() to get the inner arrays into one outer array.

SELECT array_agg(a ORDER BY x)
       FROM (SELECT x,
                    array_agg(val ORDER BY y) a
                    FROM t1
                    GROUP BY x) t;