Menu
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}}}
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?
886
Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
JSONB and IndexesPostgreSQL can use indexes for the text results as compare operands. GIN index can be used by the GIN JSONB operator class.
Because JSONB stores data in a binary format, queries process significantly faster. Storing data in binary form allows Postgres to access a particular JSON key-value pair without reading the entire JSON record. The reduced disk load speeds up overall performance. Support for indexing.
The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed.
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")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With