How to query a JSONB column

Question

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

What you show as the content of the object is not valid JSON. — jjanes
– jjanes, Commented May 8, 2022 at 15:47
What is that final query you show, is that your rails code actually produces, or is that you want it to produce? — jjanes
– jjanes, Commented May 8, 2022 at 15:51

mu is too short · Accepted Answer · 2022-05-08 16:28:25Z

1

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.

answered May 8, 2022 at 16:28

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark Denn Over a year ago

Sounds interesting, what do you mean by "fix any existing data"? How can I convert?

mu is too short Over a year ago

Pull each column value out, decode the YAML, put it back into the database as proper JSON: stackoverflow.com/a/40555436/479863

Collectives™ on Stack Overflow

How to query a JSONB column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related