0

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?

felipeecst
  • 1,355
  • 3
  • 16
  • 32
  • 1
    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 Jun 17 '21 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 Jun 17 '21 at 23:31

1 Answers1

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

felipeecst
  • 1,355
  • 3
  • 16
  • 32