'Postgres how to use ANY instead of IN

I have a query but it is not working correctly,

Select * 
from "Firms" 
Where "Properties" IN ('{1,2}')

That's my postgres query, "Properties" column is int array.

Only those containing these two values ​​are coming, but I want to fetch records containing any of the values, and I want to list by number of matching values ​​if possible.

arrayspostgresql


Solution 1:[1]

Test case:

create table array_any(id integer, array_fld int[]);

insert into array_any values (1, ARRAY[1,2]), (2, ARRAY[2,3]), (3, ARRAY[3,4]);

select id, count(*) from array_any, 
lateral unnest(array_fld) as s where s = ANY(ARRAY[1,2]) group by id order by id;
 id | count 
----+-------
  1 |     2
  2 |     1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Adrian Klaver

Related Questions