CODEWITHSUNDEEP
sqldatabasepostgresql
Hardik Gulati
Hardik Gulati

Reputation: 36

Query to provide key to result mapping on postgres arrays

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.

Upvotes: 0

Views: 109

Answers (2)

user330315
user330315

Reputation:

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);

Upvotes: 2

Andronicus
Andronicus

Reputation: 26076

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');

Upvotes: 1

Related Questions