I have a table in PostgreSQL that contains two columns, one being a bigint primary key and the other contains JSONB with a mix of arrays and simple key value pairs.

If I wanted to return the rows in the table where the JSON data in the second column contains a certain exact key value pair, what would the query need to look like? The second column is a JSONB column.

I have been reviewing the PostgreSQL documentation, but I cannot find the operators I would need to make this happen.

I have a table in PostgreSQL that contains two columns, one being a bigint primary key and the other contains JSONB with a mix of arrays and simple key value pairs.

If I wanted to return the rows in the table where the JSON data in the second column contains a certain exact key value pair, what would the query need to look like? The second column is a JSONB column.

I have been reviewing the PostgreSQL documentation, but I cannot find the operators I would need to make this happen.

• postgresql
• jsonb

Share Improve this question Follow edited Feb 3 at 5:40 Ken White 126k1515 gold badges236236 silver badges466466 bronze badges asked Feb 3 at 3:52 ma89581ma89581 1

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Your question is not totally clear on that, but if the key-value pair you are looking for is not at the top level of the JSON structure, your best option would be to use a JSONPATH search.

This example searches for a { "key": "value" } pair anywhere nested in a JSON structure:

SELECT JSONB '{
                "x": [
                       { "key": "value" },
                       { "key": "othervalue" }
                     ]
              }'
       @@ 'strict $.**.key == "value"';