Compare array against array column in postgres

Question

#  things               :string           is an Array

scope :things, ->(q) { where('ARRAY[?]::varchar[] IN things', Array.wrap(q)) }

scope :things, ->(q) { where('things && ARRAY[?]::varchar[]', Array.wrap(q)) }

scope :things, ->(q) { where('ARRAY[?]::varchar[] <@ things', Array.wrap(q)) }

I've tried a few versions, but I can't seem to find the proper incantation. I'm looking to find any row that has any of the things in the array... is there any overlap?

[1, 2, 3] & [1, 8] = t
[1, 2, 3] & [8, 9] = f

I'm trying to mimic ActiveRecord's default where behavior. If I give it an array, it'll get all the matching rows. Is this possible with postgres arrays? Is it even efficient?

&& is the "overlaps" operator for arrays so the second one should work. What do the things values look like? What does q look like? What does Model.things(q).to_sql look like with the second version of the scope? — mu is too short
– mu is too short, Commented Nov 5, 2018 at 18:24
&& does work! My sample data was wrong, so I was getting [] correctly, but thinking it was incorrect. Thanks for the help! — Dudo
– Dudo, Commented Nov 5, 2018 at 18:41

Dimitar Spasovski · Accepted Answer · 2018-11-05 18:18:10Z

2

One way of doing this is by converting the arrays to a set of rows. Once you have the arrays as set of rows, you can do an intersection between them and check if the result is empty set.

For example:

CREATE TABLE my_test_table(id BIGINT, test_array BIGINT[]);
INSERT INTO my_test_table(id, test_array)
  VALUES
  (1, array[1,2,3]),
    (2, ARRAY[1,5,8]);

SELECT * FROM my_test_table
 WHERE  array_length((SELECT array
    (
        SELECT UNNEST(test_array)
        INTERSECT
        SELECT UNNEST(array[3,15,2])
    )), 1) > 0;

The result of the SELECT statement above is:

1 | {1,2,3}

This allows for more complex matching of elements of 2 arrays. For example, if you would like to select the arrays that have at least 2 common elements, you could just change the WHERE part to

 WHERE  array_length((SELECT array
    (
        SELECT UNNEST(test_array)
        INTERSECT
        SELECT UNNEST(array[3,15,2])
    )), 1) > 1;

answered Nov 5, 2018 at 18:18

Dimitar Spasovski

2,18210 gold badges29 silver badges48 bronze badges

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

3 Comments

mu is too short Over a year ago

But array && array already does this.

Dimitar Spasovski Over a year ago

Yes, but the && operator on array returns a boolean result. So you can never know how many matches you had between the arrays. The method I explained above (the last paragraph) shows how you can do more complex checks like: at least N overlapping elements, exactly N overalapping elements and etc. Anyway, I agree that the && operator should work for the OP.

Cristian David Ippolito Over a year ago

ty @DimitarSpasovski

Collectives™ on Stack Overflow

Compare array against array column in postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related