norbertpy norbertpy - 1 year ago 89
SQL Question

PostgreSQL - get matched value of array field in the result

If I have a query that looks for a list of values in an Array column, is there a way to get the

value as a computed column in result table?

Imagine the following query:

SELECT name, tags
FROM Books
WHERE Books.tags && ARRAY['APLLE', 'ORANGE']::varchar[]

This is what I intend to have as result:

| name | tags | query | <- I WANT query COLUMN
| Cooking | {APPLE, EGGPLANT} | "APPLE" |
| Frying | {TOMATO, ORANGE} | "ORANGE" |
| Boiling | {APPLE} | "APPLE" |

Answer Source
select name, b.tags, (
    select string_agg(t,',')
        unnest(b.tags) t(t)
        inner join
        unnest(v.tags) s(t) using (t)
    ) as query
    books b
    inner join
    (values (array['APPLE', 'ORANGE']::varchar[])) v(tags) on b.tags && v.tags
  name   |       tags       | query  
 Boiling | {APPLE}          | APPLE

The data:

create table books (name text, tags varchar[]);
insert into books (name, tags) values
('Cooking','{APPLE, EGGPLANT}'),
('Frying','{TOMATO, ORANGE}'),
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download