Query nested jsonb Postgres column

Question

I have a metadata column of type jsonb.

I know how to check whether it contains a specific key:

obj = Model.create
obj.metadata = {"foo"=>"1", "bar"=>{"baz"=>{"qux"=>2}}}
obj.save

Model.where("(metadata->'bar') IS NOT NULL") # returns obj

I wonder, how would I check if there is baz key in obj.metadata['bar'] and, if I had, for deeper nested keys?

Vane

Answer 1

Ok, just found a way:

Model.where("(metadata -> 'bar' ->> 'baz') IS NOT NULL")

if metadata has more nested json:

obj.metadata = {"foo"=>"1", "bar"=>{"baz"=>{"qux"=>2}}}

and I would want to see, if there's metadata['bar']['baz']['qux']:

Model.where("(metadata -> 'bar' -> 'baz' ->> 'qux') IS NOT NULL")

Vane