PostgreSQL - Querying a multidimensional array

Question

Suppose I have the following 2d array

    SELECT
      ARRAY[
            [100,null,null,null,null,null,null,null],
            [-87.40,100,null,null,null,null,null,null],
            [-2.37,15.21,100,null,null,null,null,null],
            [-68.01,67.04,36.26,100,null,null,null,null],
            [-2.19,-3.59,-35.09,-22.58,100,null,null,null],
            [0.46,-18.19,-57.98,-34.93,45.22,100,null,null],
            [-75.67,51.66,31.01,75.60,15.85,5.37,100,null],
            [49.44,-68.09,-46.72,-58.14,67.34,68.20,-16.88,100] 
        ]::TEXT[][] AS matrix

Assume this is stored in a database table. How would I go about querying this so as to give me a row back per array element, as in 8 rows, each of which is a text array. If I use UNNEST like the following

SELECT UNNEST(t1.matrix) FROM (
   SELECT
      ARRAY[
             [100,null,null,null,null,null,null,null],
             [-87.40,100,null,null,null,null,null,null],
             [-2.37,15.21,100,null,null,null,null,null],
             [-68.01,67.04,36.26,100,null,null,null,null],
             [-2.19,-3.59,-35.09,-22.58,100,null,null,null],
             [0.46,-18.19,-57.98,-34.93,45.22,100,null,null],
             [-75.67,51.66,31.01,75.60,15.85,5.37,100,null],
             [49.44,-68.09,-46.72,-58.14,67.34,68.20,-16.88,100]    
      ]::TEXT[][] AS matrix
  ) t1;

It returns a row for each element of each sub array. The output I'm looking for would be a text array column with 8 rows, e.g,

--Desired Result
[100,null,null,null,null,null,null,null]
[-87.40,100,null,null,null,null,null,null]
[-2.37,15.21,100,null,null,null,null,null]
[-68.01,67.04,36.26,100,null,null,null,null]
[-2.19,-3.59,-35.09,-22.58,100,null,null,null]
[0.46,-18.19,-57.98,-34.93,45.22,100,null,null]
[-75.67,51.66,31.01,75.60,15.85,5.37,100,null]
[49.44,-68.09,-46.72,-58.14,67.34,68.20,-16.88,100]

As opposed to

"100"
.
.
.
.
.
.
.
"-87.40"
"100"
.

Any pointers in the right direction would be appreciated. I'm using v11.9. Thanks

Please add the expected output

Akhilesh Mishra
– Akhilesh Mishra

2021-05-24 15:28:19 +00:00
Commented May 24, 2021 at 15:28 — Akhilesh Mishra
– Akhilesh Mishra, Commented May 24, 2021 at 15:28

Akhilesh Mishra · Accepted Answer · 2021-05-24 16:04:51Z

1

Try this:

Table Query

create table test (col1 text[][]);

insert into test values('{{100,null,null,null,null,null,null,null},
             {-87.40,100,null,null,null,null,null,null},
             {-2.37,15.21,100,null,null,null,null,null},
             {-68.01,67.04,36.26,100,null,null,null,null},
             {-2.19,-3.59,-35.09,-22.58,100,null,null,null},
             {0.46,-18.19,-57.98,-34.93,45.22,100,null,null},
             {-75.67,51.66,31.01,75.60,15.85,5.37,100,null},
             {49.44,-68.09,-46.72,-58.14,67.34,68.20,-16.88,100}}');

Query--

SELECT array_agg(col1[d1][d2])
FROM   test t,generate_series(array_lower(t.col1,1), array_upper(t.col1,1)) d1
    ,  generate_series(array_lower(t.col1,2), array_upper(t.col1,2)) d2
GROUP  BY d1
ORDER  BY d1

OUTPUT:

array_agg
{100,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
{-87.40,100,NULL,NULL,NULL,NULL,NULL,NULL}
{-2.37,15.21,100,NULL,NULL,NULL,NULL,NULL}
{-68.01,67.04,36.26,100,NULL,NULL,NULL,NULL}
{-2.19,-3.59,-35.09,-22.58,100,NULL,NULL,NULL}
{0.46,-18.19,-57.98,-34.93,45.22,100,NULL,NULL}
{-75.67,51.66,31.01,75.60,15.85,5.37,100,NULL}
{49.44,-68.09,-46.72,-58.14,67.34,68.20,-16.88,100}

DEMO

edited May 24, 2021 at 16:04

answered May 24, 2021 at 15:59

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

1 Comment

Andrew Fox Over a year ago

Perfect, thank you. This is exactly what I was looking for. Much appreciated.

Collectives™ on Stack Overflow

PostgreSQL - Querying a multidimensional array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related