ActiveRecord/Postgres - find all entities with nulls in array field

Question

Given a model User and a string array column device_ids, how can I query for all Users that have nulls as an element of this array column?

E.g., some of the users has invalid values for device_ids:

user.device_ids
=> [null, "XYZABCDEFGH"]

I tried querying as suggested in this answer...

User.where.not("(-1 = ANY(device_ids)) IS NULL")

... but I get this error:

ERROR:  operator does not exist: integer = character varying

Is there any other ways to get all the users in a single query?

As far as SQL goes you can use something like EXISTS (SELECT * FROM unnest(device_ids) a(e) WHERE e IS NULL). (But I have no idea how to write that in for Rails.) But I advise against using arrays, if you cannot handle them atomically, i.e. you need to query single elements of them. Using the relational way is far less stressful. — sticky bit
– sticky bit, Commented Jun 17, 2021 at 23:05
thank you so much @stickybit! This helped me a lot. The only reason I'm querying single elements is because I need to remove the invalid ones. — felipeecst
– felipeecst, Commented Jun 17, 2021 at 23:31

felipeecst · Accepted Answer · 2021-06-17 23:34:01Z

0

Thank you for the help @sticky bit! My final solution was:

User.where("EXISTS (SELECT * FROM unnest(device_ids) a(e) WHERE e IS NULL)")

answered Jun 17, 2021 at 23:34

felipeecst

1,4253 gold badges19 silver badges34 bronze badges

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

1 Answer 1