SQL Overlap with filter inside array

Question

Previously i try to use overlap to find data where data has any of values in array. I use postgres

SQL statement was:

SELECT *,
ARRAY_AGG("reserve"."state_id" ) AS "states" 
FROM "orders_order"
JOIN "reserve" ON ("order"."id" = "reserve"."order_id")
GROUP BY "order"."id"
HAVING (ARRAY_AGG("reserve"."state_id" )&& array['test', 'test_new']::varchar(255)[])

For example if my states where like below:

{'test', 'new_test' }

It gives me all reserves if states test was in array

Now i need receive states if only ['test'] in array

Expected output must be empty in above mentioned example

Some examples to clarify:

1. {'test', 'new_test' } -> false
2. {'test', 'test' } -> true
3. {'test'} -> true

How can i achieve this. What needs to add to my SQL statement?

SQL does not support arrays in general. Tag with the database you areusing. — Gordon Linoff
– Gordon Linoff, Commented Jul 25, 2021 at 19:00

Jim Jones · Accepted Answer · 2021-07-26 08:39:41Z

1

You're looking for @> (contains operator):

SELECT 
 array['test'] @> array['test','new_test'], --false
 array['test'] @> array['test','test'],     --true
 array['test'] @> array['test'];            --true

 ?column? | ?column? | ?column? 
----------+----------+----------
 f        | t        | t

edited Jul 26, 2021 at 8:39

answered Jul 26, 2021 at 6:35

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

SQL Overlap with filter inside array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related