CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-5rails-activerecordjsonb
TomDunning
TomDunning

Reputation: 4877

Finding empty Postgres jsob sub array using rails

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?

Upvotes: 1

Views: 433

Answers (1)

mu is too short
mu is too short

Reputation: 434635

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

Upvotes: 1

Related Questions