Getting specific Array items in one row (PostgreSQL)

Question

Here's my problem:

I have a table with 4 columns (type = array, one-dimension) with values like:

   ID        c1             c2           c3

   1      {1, 2, 3}    {10, 20, 30}   {100, 200, 300}
   2      {4, 5, 6}    {11, 22, 33}   {111, 222, 333}

I was wondering if it is possible to get a result like the following:

   select ID, c1, c2, c3 from t1 where c3 = 200 (<---- pseudo-statement!)

Result:

   1, 2, 20, 200

or

   select ID, c1, c2, c3 from t1 where c3 = 333

Result:

    1, 6, 33, 333

So what I want is to get the corresponding values of the value in the where-statement.

Is there a way to get a solution for this?

Erwin Brandstetter · Accepted Answer · 2012-02-17 18:28:23Z

(Edit1: simplified, Edit2: wider scope)

Solutions for any array-length. Also returns all hits, even at multiple positions in one or more arrays at the same time.

If you know the fixed length of the one-dimensional array, you can hard-code it in generate_series(), for the simplest and fastest solution:

SELECT id, c1[i], c2[i], c3[i]
FROM   tbl, generate_series(1, 3) i
WHERE  c3[i] = 200;

If all arrays in c3 have the same unknown length, you can generalize at very little cost:

SELECT id, c1[i], c2[i], c3[i]
FROM   tbl, generate_subscripts((SELECT c3 FROM tbl LIMIT 1), 1) s(i)
WHERE  c3[i] = 200;

Note that I use the convenient function generate_subscripts() here.
For a greater subscripts than the upper limit, array elements are just NULL and drop out of the query.

If the unknown length of the array in c3 actually varies, you can generalize some more, at a higher cost:

SELECT id, c1[i], c2[i], c3[i]
FROM   tbl
FROM   tbl, generate_series(1, (SELECT max(array_upper(c3, 1)) FROM tbl)) s(i)
WHERE  c3[i] = 200;

Finally, for non-standard array subscripts in addition, the same could be done like this:

SELECT id, c1[i], c2[i], c3[i]
FROM   tbl
     , generate_subscripts((SELECT c3 FROM tbl
                            ORDER  BY array_length(c3,1) DESC LIMIT 1), 1) s(i)
WHERE  c3[i] = 200;

Instant testbed:

WITH tbl (id, c1, c2, c3) AS (
    VALUES
     (1, '{1, 2, 3}'::int[], '{10, 20, 30}'::int[], '{100, 200, 300}'::int[])
    ,(2, '{4, 5, 6}', '{11, 22, 33}', '{111, 222, 333}')
-- un-comment to test 333 in all positions of c3 and varying array length
--  ,(3, '{7, 8, 9, 10}', '{14, 25}', '{333, 333, 333, 333}')
    )
SELECT id, c1[i], c2[i], c3[i]
FROM tbl
    ,generate_subscripts((SELECT c3 FROM tbl
                          ORDER  BY array_length(c3,1) DESC LIMIT 1), 1) s(i)
WHERE  c3[i] = 333;

jakubiszon · Accepted Answer · 2012-02-17 15:06:37Z

1

You need to use generate_series to produce a cross join of your table and all indexes of your arrays (here it will be 1,2,3) :

select *
from (
  select yourtab.id
  ,      yourtab.c1[tidx.idx]
  ,      yourtab.c2[tidx.idx]
  ,      yourtab.c3[tidx.idx]
  from yourtab
  cross join generate_series(1, 3) tidx(idx)
) t(id, c1, c2, c3)
where c3 = 333

Note the asumption that each array will always have 3 elements.

edited Feb 17, 2012 at 15:06

answered Feb 17, 2012 at 15:00

jakubiszon

3,5831 gold badge33 silver badges44 bronze badges

1 Comment

Stone Over a year ago

Really an elegant solution! Thank you!

Collectives™ on Stack Overflow

Getting specific Array items in one row (PostgreSQL)

2 Answers 2

Instant testbed:

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Instant testbed:

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related