Query on JSON object keys in ActiveRecord/Postgresql

Question

I have a json column in an Answer table in a rails app; document. I'm trying to write a scope which returns true if the document is empty, or if it contains only one of two specific keys.

With ruby we could do something like:

Answer.document.except(:name, :secondary_name).empty?

But I need to use a scope that should behave like this:

a1 = Answer.document => {}
a2 = Answer.document => { name: "anything" }
a3 = Answer.document => { secondary_name: "anything" }
a4 = Answer.document => { other_important_key: "other_important_data" }  

Answers.untouched => [a1, a2, a3]

See Active Record and PostgreSQL and JSON Functions and Operators. The ActiveRecord query interface doesn't cover querying inside of JSON types as there are two many differences between vendors and dealing with JSON types is a royal PITA. Either use a string, arel or use actual tables instead of JSON. — max
– max, Commented Dec 10, 2024 at 19:07

Phil Brockwell · Accepted Answer · 2024-12-11 11:00:09Z

0

In the end I've inverted the query (to return touched answers) using the following:

where("(document::jsonb - 'name' - 'secondary_name') <> '{}'::jsonb")

answered Dec 11, 2024 at 11:00

Phil Brockwell

4657 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

Query on JSON object keys in ActiveRecord/Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related