sql - How do I return a jsonb array and array of objects from my data? -

i have following table:

create table mytable (   id       serial primary key , employee text unique not null , data     jsonb ); 

with following data:

insert mytable (employee, data) values  ('jim', '{"sales_tv": [{"value": 10, "yr": "2010", "loc": "us"}, {"value": 5, "yr": "2011", "loc": "europe"}, {"value": 40, "yr": "2012", "loc": "asia"}], "sales_radio": [{"value": 11, "yr": "2010", "loc": "us"}, {"value": 8, "yr": "2011", "loc": "china"}, {"value": 76, "yr": "2012", "loc": "us"}], "another_key": "another value"}'),  ('rob', '{"sales_radio": [{"value": 7, "yr": "2014", "loc": "japan"}, {"value": 3, "yr": "2009", "loc": "us"}, {"value": 37, "yr": "2011", "loc": "us"}], "sales_tv": [{"value": 4, "yr": "2010", "loc": "us"}, {"value": 18, "yr": "2011", "loc": "europe"}, {"value": 28, "yr": "2012", "loc": "asia"}], "another_key": "another value"}') 

notice there other keys in there besides "sales_tv" , "sales_radio". queries below need focus on "sales_tv" , "sales_radio".

i need find sales jim 2012. starts "sales_" , put in object (just need product sold , value). e.g.:

    employee   | sales_     jim        | {"sales_tv": 40, "sales_radio": 76} 

i've got:

select * mytable,   (select l.key, l.value mytable, lateral jsonb_each_text(data) l     key 'sales_%') a,   jsonb_to_recordset(a.value::jsonb) d(yr text, value float)   mytable.employee = 'jim'   , d.yr = '2012' 

but can't seem jim's data. instead get:

employee | key         |  value -------- |------       | ----- jim      | sales_tv    |  [{"yr": "2010", "loc": "us", "value": 4}, {"yr": "2011", "loc": "europe", "value": 18}, {"yr": "2012", "loc": "asia", "value": 28}] jim      | sales_tv    |  [{"yr": "2010", "loc": "us", "value": 10}, {"yr": "2011", "loc": "europe", "value": 5}, {"yr": "2012", "loc": "asia", "value": 40}] jim      | sales_radio |  [{"yr": "2010", "loc": "us", "value": 11}, {"yr": "2011", "loc": "china", "value": 8}, {"yr": "2012", "loc": "us", "value": 76}] 

you treat result of first join json, not text string, use jsonb_each() instead of jsonb_each_text():

select t.employee, json_object_agg(a.k, d.value) sales   mytable t join   lateral jsonb_each(t.data) a(k,v) on a.k 'sales_%' join   lateral jsonb_to_recordset(a.v) d(yr text, value float) on d.yr = '2012'  t.employee = 'jim'  -- works because employee unique group  1; 

group 1 shorthand group t.employee.
result:

employee | sales ---------+-------- jim      | '{ "sales_tv" : 40, "sales_radio" : 76 }' 

i untangled , simplified query.

json_object_agg() instrumental in aggregating name/value pairs json object. optionally cast jsonb if need - or use jsonb_object_agg() in postgres 9.5 or later.

using explicit join syntax attach conditions in obvious place.
the same without explicit join syntax:

select t.employee, json_object_agg(a.k, d.value) sales   mytable t      , jsonb_each(t.data)      a(k,v)       , jsonb_to_recordset(a.v) d(yr text, value float)  t.employee = 'jim' ,    a.k 'sales_%' ,    d.yr = '2012' group  1;