Postgres, where in on a json object

Question

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.

shantr · Accepted Answer · 2022-09-01 11:25:38Z

1

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

answered Sep 1, 2022 at 11:25

shantr

9544 silver badges8 bronze badges

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

Comments

user330315 · Accepted Answer · 2022-09-01 11:24:36Z

1

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

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

answered Sep 1, 2022 at 11:24

user330315

Collectives™ on Stack Overflow

Postgres, where in on a json object

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related