CODEWITHSUNDEEP
sqljsonpostgresqlpostgresql-9.6
Slavik Muz
Slavik Muz

Reputation: 1217

Querying a JSON array in Postgres

I have a table with json field

CREATE TABLE orders (
                      ID serial NOT NULL PRIMARY KEY,
                      info json NOT NULL
);

I inserted data to it

INSERT INTO orders (info)
VALUES
(
  '{"interestedIn":[11,12,13],"countries":["US", "UK"]}'
);

How can I get rows where intrestedIn in(11, 12) and countries in("US")?

Upvotes: 0

Views: 62

Answers (2)

jjanes
jjanes

Reputation: 44373

Your use of "in" in the problem specification is confusing, as you seem to be using it in some SQL-like pseudo syntax, but not with the same meaning it has SQL.

If you want rows where one of the countries is US and where both 11 and 12 are contained in "interestedIn", then it can be done in a single containment operation:

select * from orders where info::jsonb @> '{"interestedIn":[11,12],"countries":["US"]}'

If you want something other than that, then please explain in more detail, and provide both examples which should match and examples which should not match (and tell us which is which).

Upvotes: 1

user330315
user330315

Reputation:

Checking for the country is quite simple as that is a text value:

select *
from orders
where info::jsonb -> 'countries' ? 'US'

Adding the condition for the integer value is a bit more complicated because the ? operator only works with strings. So you need to unnest the array:

select o.*
from orders o
where o.info::Jsonb -> 'countries' ? 'US'
  and exists (select *
              from json_array_elements_text(o.info -> 'interestedIn') as x(id)
              where x.id in ('11','12'));

If you also might need to check for multiple country values, you can use the ?| operator:

select o.*
from orders o
where o.info::jsonb -> 'countries' ?| array['US', 'UK']
  and exists (select *
              from json_array_elements_text(o.info -> 'interestedIn') as x(id)
              where x.id in ('11','12'));

Online example: https://rextester.com/GSDTOH43863

Upvotes: 0

Related Questions