CODEWITHSUNDEEP
jsonpostgresql
blackmoon
blackmoon

Reputation: 381

postgresql json - seach if arrays includes an element

Im newbie,

i have 2 json's:

  1. alergy_to

    {"0":"carrot","1":"banana","2": "pepper"}

  2. 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

Answers (1)

Dzmitry Savinkou
Dzmitry Savinkou

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

Related Questions