Question 114855 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres, where in on a json object

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.

Tags:

2

Answers


  1. 0

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

    select *
from test
where features ->> 'country' in ('Sweden', 'Norway')
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search