CODEWITHSUNDEEP
jsonpostgresqljsonbpostgresql-9.5
B Seven
B Seven

Reputation: 45943

How to query the values in a jsonb column in PostgreSQL?

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

Upvotes: 1

Views: 1061

Answers (1)

klin
klin

Reputation: 121474

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.

Upvotes: 1

Related Questions