CODEWITHSUNDEEP
sqlarrayspostgresqljsonb
Sequinex
Sequinex

Reputation: 671

Where condition on list of jsonb in Postgres

Let's say I have the table food_articles, which has the following columns: name(type ="text") and ingredients(type="jsonb[]"), where the ingredient jsonb object looks like this:

{
    ingredient_id: 'string',
    quantity: 'number'
}

How would I create a query that returns all rows in food_articles that has the ingredient_id = 1337 in the ingredients jsonb array?

Upvotes: 0

Views: 1925

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

In the where clause unnest ingredients into a table and check whether a record exists with ingredient_id = '1337'.

select * from food_articles 
where exists
(
 select 
 from unnest(ingredients) arj 
 where arj ->> 'ingredient_id' = '1337' 
);

Please note that if the type of ingredients is jsonb that contains an array like this one '[{"ingredient_id":"1337","quantity":1},{"ingredient_id":"1336","quantity":2}]' rather than a Postgres array of jsonb elements (i.e. jsonb[]) then you can use the 'contains' @> operator simply as

where ingredients @> '[{"ingredient_id":"1337"}]'

Upvotes: 1

Stefan
Stefan

Reputation: 1928

create table "food_articles" ("name" text, "ingredients" jsonb);

insert into "food_articles" values('food 1', jsonb_build_object('ingredient_id', 1, 'quantity', 1)),
 ('food 2', jsonb_build_object('ingredient_id',2, 'quantity',1337)),
('food 3', jsonb_build_object('ingredient_id',3, 'quantity',1337)),
('food 3', jsonb_build_object('ingredient_id', 3, 'quantity',1332));

select * from "food_articles"
where "ingredients"->>'quantity'='1337';

Playground link: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=b6ab520a44c65656ebc767a10f5737b6

Documentation for postgresql jsonb manipulation: https://www.postgresql.org/docs/9.5/functions-json.html

Upvotes: 0

Related Questions