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[][]

sticky bit · Accepted Answer · 2019-04-01 20:46:01Z

5

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;

answered Apr 1, 2019 at 20:46

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

karianpour · Accepted Answer · 2019-04-01 20:58:10Z

This is one way :

select array_agg(dimension1 order by x) as dimension2
from (
  select b.x, array_agg(t1.val order by a.y) as dimension1
  from 
    (select y from t1 group by y) a
  full join (select x from t1 group by x) b on true
  left join t1 on a.y = t1.y and b.x = t1.x
  group by b.x
) a;

This is similar and modern one :

with 
  a as (select y from t1 group by y),
  b as (select x from t1 group by x)
select array_agg(dimension1  order by x) as dimension2
from (
  select b.x, array_agg(t1.val order by a.y) as dimension1
  from a
  full join b on true
  left join t1 on a.y = t1.y and b.x = t1.x
  group by b.x
  order by b.x
) c;

to check if it gives null delete one row:

delete from t1 where x = 'A' and y = 'B';

and you should get :

{{1,NULL,3},{4,5,6},{7,8,9}}

Collectives™ on Stack Overflow

Postgres table to two-dimensional array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related