Search code examples
jsonpostgresqlselectcontains

select from string from outcome of json file in postgresql

In postgre sql my table has attributes column with json format below

{"color": true,"view": [184],"school": 
[805,812,855,856,857]}

I would like write a column that if the school contains 805,812,855 then 1 else 0. Below I tried but it is not working.

 case when  json_extract_path_text(attributes, 'school') in 
 [805,812,855] then 1 else 0 end as school

How can I fix this? Thank you.

Solution

  • You can use a lateral cross join to create an array for each row and see if it contains the values in [805,812,855]. Here, I'm assuming you want to make sure it contains ALL the values, not ANY of the values.

    with data as (select * from (values
  ('{"color": true,"view": [184],"school":[805,812,855,856,857]}'::jsonb),
  ('{"color": true,"view": [184],"school":[805,812]}'::jsonb)
) as v(data))
SELECT school_arr @> '{805,812,855}'
FROM data
CROSS JOIN LATERAL (SELECT ARRAY(SELECT jsonb_array_elements_text(data -> 'school'))) d(school_arr)
;
 ?column?
----------
 t
 f
(2 rows)