Unnest multi dimensional array by one level in Postgresql

Question

Let's say I have this three dimensional array:

ARRAY[[['abc', 'def'], ['ghi', 'jkl']], [['mno', 'pqr'], ['stu', 'vwx']]]

How can I get {{abc,def},{ghi,jkl}} out of it?

If I try:

SELECT (ARRAY[[['abc', 'def'], ['ghi', 'jkl']], [['mno', 'pqr'], ['stu', 'vwx']]])[1:1]

I get {{{abc,def},{ghi,jkl}}}, but I need {{abc,def},{ghi,jkl}}.

There are answers to similar questions, that solve the issue for 2-dimensional arrays, such as this one, but those don't work for n-dimensional arrays.

This is such a basic task, but in PostgreSQL it seems to be quite tricky.

score 1 · Answer 1 · answered May 10 '19 at 19:46

I'm really hoping someone can provide a more straightforward method of doing this, but here's my shot:

  with my_array as 
      (select ARRAY[[['abc', 'def'], ['ghi', 'jkl']], [['mno', 'pqr'], ['stu', 'vwx']]] as arr),
  deconstructed_array as (
    select a.elem, a.nr, x, y, z
    FROM my_array, unnest(arr) WITH ordinality as a(elem, nr) 
    JOIN (
        SELECT x, y, z, row_number() over (ORDER BY x, y, z) as row
        FROM my_array,
        generate_series(array_lower(arr, 1), array_upper(arr, 1)) g1(x),
        generate_series(array_lower(arr, 2), array_upper(arr, 2)) g2(y),
        generate_series(array_lower(arr, 3), array_upper(arr, 3)) g3(z)
    ) array_dims ON nr = row
)
select array_agg(elems) FROM (
   select array_agg(elem) as elems, x, y
   FROM deconstructed_array GROUP BY x, y
) first_level 
WHERE x = 1
group by x
;
       array_agg
-----------------------
 {{abc,def},{ghi,jkl}}
(1 row)

Explanation: We use generate_series to associate the various dimensions of the array with unnested rows. Unfortunately, we need to know that this is a three dimensional array in this case, but the length of each array shouldn't matter. The output of that second CTE looks something like this:

 elem | nr | x | y | z
------+----+---+---+---
 abc  |  1 | 1 | 1 | 1
 def  |  2 | 1 | 1 | 2
 ghi  |  3 | 1 | 2 | 1
 jkl  |  4 | 1 | 2 | 2
 mno  |  5 | 2 | 1 | 1
 pqr  |  6 | 2 | 1 | 2
 stu  |  7 | 2 | 2 | 1
 vwx  |  8 | 2 | 2 | 2

From there, we just use array_agg to group the arrays back together.

Unnest multi dimensional array by one level in Postgresql

1 Answers1