Search code examples
jsonpostgresqljsonb

Postgres searching with JSONB field for keys and/or values of json object

I am looking for find how to correctly query a Postgres JSONB field. Suppose I have a JSON object like so

{"key1": ["value1", "value2"], "key2": ["value1", "value3"]}

And I'm storing it in the field 'data', I can query for the existence of the key.

SELECT data from somethings WHERE data ? "key1"

Or the key and the value.

SELECT data from somethings WHERE data -> "key1" ? "value1"

But I am struggling to search by the existence of the key values. I'm looking for something like. Basically I want to find the existence of a value whether is a top-level key or one of the values in each array.

SELECT data from somethings WHERE data ? ".*" -> "value1"

I thought I was looking for jsonb_each for a bit but I am unsure how to leverage it. Any thoughts?

Solution

  • You can use a JSON path expression:

    select *
from something
where data @? '$.* ? (@[*] == "value1")'

    The $.* iterates over all keys, and the @[*] then iterates over all array elements for each key.