Return true if any value in an specific key inside a jsonb array exists The rows in my table look like

 facility | events
----------+---------------------------------------------------------------
  1       | [{ "id" : "1", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "good"}]


I want to get all the rows who have a severity = bad

I tried 

select facility, events::jsonb @> '"severity"' as bad from tbl limit 10;

and I got (same data as before)

 facility | bad
----------+-------
  1       | f


Thanks in advance
I think you can get it by using jsonb_array_elements function and the ->> operator.




create table test (facility int, data jsonb);

insert into test values 
(1, '[{ "id" : "1", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(2, '[{ "id" : "2", "count" : "4", "severity" : "good" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(3, '[{ "id" : "3", "count" : "4", "severity" : "good" }, { "id" : "3", "count": "4", "severity" : "bad"}]');






SELECT t1.facility, obj-&gt;&gt;'severity' severity
FROM   (SELECT facility, jsonb_array_elements(data) obj
        FROM   test) t1
WHERE  obj-&gt;&gt;'severity' = 'bad'



facility | severity
-------: | :-------
       1 | bad     
       3 | bad     



dbfiddle here

In case there is more than one "severity" : "bad" for the same facility you could avoid duplicates using DISTINCT.




create table test (facility int, data jsonb);

insert into test values 
(1, '[{ "id" : "1", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(2, '[{ "id" : "2", "count" : "4", "severity" : "good" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(3, '[{ "id" : "3", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "bad"}]');






SELECT distinct t1.facility, obj-&gt;&gt;'severity' severity
FROM   (SELECT facility, jsonb_array_elements(data) obj
        FROM   test) t1
WHERE  obj-&gt;&gt;'severity' = 'bad'
ORDER BY t1.facility



facility | severity
-------: | :-------
       1 | bad     
       3 | bad     



dbfiddle here