norbertpy norbertpy - 3 months ago 12
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

matched
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
select name, b.tags, (
    select string_agg(t,',')
    from
        unnest(b.tags) t(t)
        inner join
        unnest(v.tags) s(t) using (t)
    ) as query
from
    books b
    inner join
    (values (array['APPLE', 'ORANGE']::varchar[])) v(tags) on b.tags && v.tags
;
  name   |       tags       | query  
---------+------------------+--------
 Cooking | {APPLE,EGGPLANT} | APPLE
 Frying  | {TOMATO,ORANGE}  | ORANGE
 Boiling | {APPLE}          | APPLE

The data:

create table books (name text, tags varchar[]);
insert into books (name, tags) values
('Cooking','{APPLE, EGGPLANT}'),
('Frying','{TOMATO, ORANGE}'),
('Boiling','{APPLE}');