Query to provide key to result mapping on postgres arrays

Question

I have a table like this

multi_id   |      value
------------------------------------
{V1,V2,V3} | PD1
{V4,V5}    | PD2 
{V6,V7}    | PD3

Now I want the value where id = V2 and V7. For that I have written a query like

select * from some_table where multi_id && ‘{“V2”,“V7"}’;

This is giving me result as :

multi_id   |      value
------------------------------------
{V1,V2,V3} | PD1
{V6,V7}    | PD3

but what I want is:

some_column   |      value
------------------------------------
V2            | PD1
V7            | PD3

Any help would be greatly appreciated.

score 2 · Accepted Answer · 2020-03-03 07:42:07Z

2

Use unnest:

select t.some_column, st.value
from unnest(array['V1','V7']) as t(some_column)
  join some_table st on t.some_column = any(st.multi_id);

answered Mar 3, 2020 at 7:36

user330315

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

Comments

Andronicus · Accepted Answer · 2020-03-03 07:35:54Z

1

You can use unnest:

select t.id, t.value
from (
    select unnest(multi_id), value
    from table_name tn
) as t(id, value)
where t.id in ('V2', 'V7');

answered Mar 3, 2020 at 7:35

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges