Im newbie,
i have 2 json's:
alergy_to
{"0":"carrot","1":"banana","2": "pepper"}
food_constains
{"0":"banana", "1": "milk"}
how to check if my "food_constains" json field constains any alergens which is listed in alergy_to json
and return true or false, or similar value
EDITED ANSWER
WITH alergy_to(data) AS ( VALUES
('{"0":"carrot","1":"banana","2": "pepper"}'::JSON)
), food_contains(data) AS ( VALUES
('{"0":"banana", "1": "milk"}'::JSON)
)
SELECT
CASE WHEN EXISTS(
SELECT true
FROM alergy_to at,json_each_text(at.data) alergy_item,
food_contains fc, json_each_text(fc.data) food_item
WHERE food_item.value = alergy_item.value
)
THEN TRUE
ELSE FALSE
END;
For more details you can refer to JSON Postgres documentation
WITH alergy_to(data) AS ( VALUES ('["carrot","banana","pepper"]'::JSON)), food_contains(data) AS ( VALUES ('["banana", "milk"]'::JSON)) SELECT TRUE as IHAVEALERGENS FROM alergy_to at,json_array_elements_text(at.data) alergy_item, food_contains fc, json_array_elements_text(fc.data) food_item WHERE food_item.value = alergy_item.value group by IHAVEALERGENS;
SELECT true is not a good idea, because the query returns as many rows as matched values. In particular, it returns no rows if there are no matched values. If you use an aggregate function, e.g. SELECT count(*) > 0, you always get one row with a boolean value.EXISTSis probably the best approach but SELECT EXISTS(..); would be just perfect.