PostgreSQL - get matched value of array field in the result

Question

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"  |

Can you use plsql or just standard SQL?

Vlad Călin Buzea
– Vlad Călin Buzea

2016-08-18 07:01:26 +00:00
Commented Aug 18, 2016 at 7:01 — Vlad Călin Buzea
– Vlad Călin Buzea, Commented Aug 18, 2016 at 7:01
@VladCălinBuzea, yep.

Sam R.
– Sam R.

2016-08-18 07:04:25 +00:00
Commented Aug 18, 2016 at 7:04 — Sam R.
– Sam R., Commented Aug 18, 2016 at 7:04

Clodoaldo Neto · Accepted Answer · 2016-08-18 12:47:52Z

2

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

edited Aug 18, 2016 at 12:47

answered Aug 18, 2016 at 12:38

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Comments

Vlad Călin Buzea · Accepted Answer · 2016-08-18 08:18:45Z

2

Try

CREATE OR REPLACE FUNCTION array_intersect (array1 varchar[], array2 varchar[]) RETURNS varchar[] 
  AS $$ 
     DECLARE 
       out VARCHAR[];
       i varchar;
     BEGIN 
        IF array1 IS NULL OR array2 IS NULL THEN 
           RETURN NULL; 
        END IF; 
        FOR i IN array1 LOOP 
           IF (i = ANY (array2)) THEN 
              out := array_append(out,i); 
           END IF; 
        END LOOP; 
        RETURN out; 
     END; 
$$ LANGUAGE PLPGSQL;

And then

SELECT name, tags,  array_intersect(tags, ARRAY['APLLE', 'ORANGE']::varchar[])
FROM Books

edited Aug 18, 2016 at 8:18

answered Aug 18, 2016 at 7:19

Vlad Călin Buzea

5595 silver badges20 bronze badges

4 Comments

Sam R. Over a year ago

Can you point to the docs? I don't seem to find array_intersect.

Vlad Călin Buzea Over a year ago

I updated my answer. It seems that array_intersect is not a standard function

Abelisto Over a year ago

create or replace function array_intersect(anyarray, anyarray) returns anyarray immutable language sql as $$   select array_agg(t1.v) from unnest($1) as t1(v) join unnest($2) as t2(v) on (t1.v=t2.v) $$;

Patrick Over a year ago

You should declare the function STRICT so that you do not have to test for NULL parameter values. Otherwise, this will also produce a row where a book does not have any of the tags in the array (query column will be NULL) which is different from what the OP posted and it is very inefficient.

Patrick · Accepted Answer · 2016-08-18 11:01:27Z

2

SELECT name, tags, fruit AS query
FROM Books
JOIN (VALUES ('APPLE', 'ORANGE')) f(fruit) ON true
-- or JOIN unnest(ARRAY['APPLE', 'ORANGE']::varchar[]) f(fruit) ON true if so desired
JOIN unnest(Books.tags) b(tags) ON b.tags = f.fruit;

This will give you multiple rows for a single book if more than one of the book's tags matches the array values. If you want to avoid that use:

SELECT name, tags, string_agg(fruit, ', ') AS query
...
GROUP BY name, tags;

answered Aug 18, 2016 at 11:01

Patrick

33k7 gold badges73 silver badges102 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - get matched value of array field in the result

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related