Postgres, where in on a json object-CodePudding

I've been looking around and can't seem to find anything that is helping me understand how I can achieve the following. (Bear in mind I have simplified this to the problem I'm having and I am only storing simple JSON objects in this field)

Given I have a table "test" defined

CREATE TABLE test (
    id int primary key
,   features jsonb
)

And some test data

id	features
1	{"country": "Sweden"}
2	{"country": "Denmark"}
3	{"country": "Norway"}

I've been trying to filter on the JSONB column "features". I can do this easily with one value

SELECT *
FROM test
WHERE features @> '{"country": "Sweden"}'

But I've been having troubles working out how I could filter by multiple values succintly. I can do this

SELECT *
FROM test
WHERE features @> '{"country": "Sweden"}'
OR features @> '{"country": "Norway"}'

But I have been wondering if there would be an equivalent to WHERE IN ($1, $2, ...) for JSONB columns.

I suspect that I will likely need to stick with the WHERE... OR... but would like to know if there is another way to achieve this.

CodePudding user response：

You can use jsonb->>'field_name' extract a field as text, then you use any operator compatible with text type

SELECT *
FROM test
WHERE features->>'country' = 'Sweden'

SELECT *
FROM test
WHERE features->>'country' in ('Sweden', 'Norway')

You an also directly work with jsonb as follow

jsonb->'field_name' extract field as jsonb, then you can use operator compatible with jsonb:

SELECT *
FROM test
WHERE features->'country' ?| array['Sweden', 'Norway']

See docs for more details

CodePudding user response：

You can extract the country value, then you can use a regular IN condition:

select *
from test
where features ->> 'country' in ('Sweden', 'Norway')