Search code examples
postgresqljsonbpostgresql-9.5

can't select data from a dictionary inside a list

I have two jsonb fields in the table below and I would like to do a query where I filter any key of the dictionary.

Table with data

My problem is that those dictionaries are inside of a list and when I try to access them with:

SELECT *
FROM public.monitoring_environmentalcontrol
WHERE celery_status->'queue'='0'

I get nothing:

Result table

Solution

  • You can use jsonb_array_elements DOC function to achieve your goal plus a LATERAL JOIN 7.2.1.5. LATERAL Subqueries on it:

    This is the setup I created:

    create table test (
  id int, 
  celery_status jsonb
);

insert into test values 
(1,'[{"queue":"a"}, {"queue":"b"}, {"queue":"c"}]'),
(2,'[{"queue":"d"}, {"queue":"e"}, {"queue":"f"}]'),
(3,'[{"queue":"g"}, {"queue":"h"}, {"queue":"i"}]');

    This is the query:

    select t.id, t.celery_status, obj->>'queue'
from test t
     join lateral
     jsonb_array_elements(t.celery_status) obj(value) on obj->>'queue' = 'a'

    You can see it working here: http://sqlfiddle.com/#!17/bf7bf/6