How to query a JSONB column-CodePudding

I am building a Rails 5.2 app. I am using Postgres. In this app I have a User object and in this object i got a JSONB column.

I need to be able to query specific attributes within the JSONB object.

This is how I define it in the User model:

serialize :preferences
store_accessor :preferences, PREFERENCES

This is the content of the object:

{"digest_is_active"=>true, "digest_frequency"=>"modal", "digest_time"=>"10:00"}

I tried this:

scope :digest_active, -> { where("preferences @> ?", { "digest_is_active": true }.to_json) }

User.where("preferences->>'digest_time' = ?", "10:00")

The query seems to run without errors but cannot find the User object.

 SELECT  "users".* FROM "users" WHERE (preferences @> '{"digest_is_active":true}') LIMIT $1  [["LIMIT", 11]]

CodePudding user response：

Don't do this:

serialize :preferences

That will encode your Ruby hash as a blob of YAML text and then that string will go into your jsonb column. A string is valid JSON so the database will be fine with this but it won't understand the internal structure so the database's JSON operators won't be much use.

Remove the serialize from your model, fix any existing data, and you should have more luck with your queries.