I have a jsonb column on my Model, which contains (among other things) an array of scores. I'd like to find all records where the scores array is empty. The data looks like this:
Model.new(results: {scores: [], other: [], info: {things: 'stuff'}})
Unfortunately trying to query for [] converts to searching for NULL.
Model.where("results -> 'scores' = ?", []).to_sql
#=> "SELECT \"model\".* FROM \"model\" WHERE NOT (results -> 'scores' = NULL)"
How do I do this?
ActiveRecord's knowledge of and integration with PostgreSQL's more advanced types (ranges, JSON, ...) is incomplete because AR doesn't understand the "results -> 'scores' = ?" SQL string, AR just sees a string that (presumably) contains SQL and a single placeholder. Consequently, AR doesn't understand that [] should be converted to JSON when it replaces the placeholder, it just does does what it always does with arrays: empty arrays become NULL, non-empty arrays become comma delimited lists (for use with in (?) constructs); for example:
Model.where("results -> 'scores' = ?", []).to_sql
#=> "SELECT \"model\".* FROM \"model\" WHERE NOT (results -> 'scores' = NULL)"
Model.where("results -> 'scores' = ?", [6,11,23]).to_sql
#=> "SELECT \"model\".* FROM \"model\" WHERE NOT (results -> 'scores' = 6,11,23)"
You can get around this by helping AR with a #to_json call and then relying on PostgreSQL's automatic type casting:
Model.where("results -> 'scores' = ?", [].to_json).to_sql
#=> "SELECT \"model\".* FROM \"model\" WHERE NOT (results -> 'scores' = '[]')"
Model.where("results -> 'scores' = ?", [6,11,23].to_json).to_sql
#=> "SELECT \"model\".* FROM \"model\" WHERE NOT (results -> 'scores' = '[6,11,23]')"