ruby-on-railspostgresqlactiverecordpsql

Query on JSON object keys in Rails/ActiveRecord/Postgresql

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

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

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