Reputation: 381
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
Upvotes: 2
Views: 82
Reputation: 5190
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
Upvotes: 1