Given a jsonb column called pairs with data such as the following in a single record:
{ "foo": 1, "bar": 2 }
How to query for records where a given value is one of the values in the above field.
For example, query for 1 would match the above record.
Query for 3 would not match.
PostgreSQL 9.5
In Postgres 9.5 use the function jsonb_each_text() in a lateral join:
with my_table(pairs) as (
values
('{ "foo": 1, "bar": 2 }'::jsonb)
)
select t.*
from my_table t
cross join jsonb_each_text(pairs)
where value = '1';
Upgrade to Postgres 12 and use json path functions, e.g.:
select *
from my_table
where jsonb_path_exists(pairs, '$.* ? (@ == 1)')
Read more: JSON Functions and Operators.
with and cross join clauses? Only using select and left join or inner join?
with statement is not necessary, in this example it is like your original table. The cross join does not mean the Cartesian product here because it is a lateral join. But you can replace it with left join if you wish. See Db<>fiddle.
jsonb_each_text() in a WHERE clause but the lateral join is simpler and faster. The main point is that there is no function in 9.5 like jsonb_path_exists(), so you have to use the set returning function jsonb_each_text().