5

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.

1 Answer 1

1

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.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.