CODEWITHSUNDEEP
ruby-on-railspostgresql
Mark Denn
Mark Denn

Reputation: 2304

How to query a JSONB column

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]]

Upvotes: 1

Views: 1617

Answers (1)

mu is too short
mu is too short

Reputation: 434665

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.

Upvotes: 1

Related Questions