rails, postgres & jsonb; find if key exists using where

Let’s assume that we have an ActiveRecord model called Day with jsonb field called references.

In the references field we stores property ids with some references, for example:

references: {'1' => 'ref123', '2' => 'ref456'}

If we want to find all days that does not have reference for a specific property id we can do this with following command:

Day.where("(references ->> '?')::text IS NULL", property.id)

Tweet