Where condition on list of jsonb in Postgres-CodePudding

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?

CodePudding user response：

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' 
);

In case that the type of ingredients is jsonb that contains an array and not a Postgres array of jsonb elements then replace unnest with jsonb_array_elements.

CodePudding user response：

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