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?
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"}]'
@> operator does not work, it says: "DETAIL: "[" must introduce explicitly-specified array dimensions." Even though the column is of type jsonb[]
jsonb, not jsonb[] column.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
