0

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

1

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->>'severity' severity
FROM   (SELECT facility, jsonb_array_elements(data) obj
        FROM   test) t1
WHERE  obj->>'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->>'severity' severity
FROM   (SELECT facility, jsonb_array_elements(data) obj
        FROM   test) t1
WHERE  obj->>'severity' = 'bad'
ORDER BY t1.facility
facility | severity
-------: | :-------
       1 | bad     
       3 | bad     

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.