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

Question

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?

user330315 · Accepted Answer · 2023-02-01 13:48:38Z

3

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.

answered Feb 1, 2023 at 13:48

user330315

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

1 Comment

RobL Over a year ago

That is exactly what I needed. I am using this in a Rails app so I had to cope with the fact the query needs specific quoting. But apart from that plain sailing. Thank you.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related