How do I return an array of objects from jsonb?

Question

I have the following table:

CREATE TABLE mytable (
  id       serial PRIMARY KEY
, employee text UNIQUE NOT NULL
, data     jsonb
);

With the following data:

INSERT INTO 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 that there are other keys in there besides just "sales_tv" and "sales_radio". For the queries below I just need to focus on "sales_tv" and "sales_radio".

I'm trying to return a list of objects for Jim for anything that starts with "sales_". In each object w/in the list I just need to return the value and the yr (ignoring "location" or any other keys) e.g.:

    employee |  sales_
    Jim      |  {"sales_tv": [{"value": 10, "yr": "2010"}, {"value": 5, "yr": "2011"}, {"value": 40, "yr": "2012"}],
                 "sales_radio": [{"value": 11, "yr": "2010"}, {"value": 8, "yr": "2011"}, {"value": 76, "yr": "2012"}]}

I am able to get each of the values but without the year nor the list format I'd like:

SELECT t.employee, json_object_agg(a.k, d.value) AS sales
FROM   mytable t
     , jsonb_each(t.data)      a(k,v) 
     , jsonb_to_recordset(a.v) d(yr text, value float)
WHERE  t.employee = 'Jim'
AND    a.k LIKE 'sales_%'
GROUP  BY 1

Results:

employee    | sales
----------  | --------
Jim         | { "sales_tv" : 10, "sales_tv" : 5, "sales_tv" : 40, "sales_radio" : 11, "sales_radio" : 8, "sales_radio" : 76 }

score 0 · Accepted Answer · edited Jul 25 '15 at 19:27

The principle is the same as the question you asked yesterday, the first query (even though this question is yesterday's second query): peel away layers of hierarchy in your json data and then re-assemble it with whatever data you are interested in, into whatever new json format.

SELECT employee, json_object_agg(k, jarr) AS sales
FROM (
  SELECT t.employee, a.k,
         json_agg(json_build_object('value', d.value, 'yr', d.yr)) AS jarr
  FROM mytable t,
       jsonb_each(t.data) a(k, v),
       jsonb_to_recordset(a.v) d(yr text, value float)
  WHERE t.employee = 'Jim'
    AND a.k like 'sales_%'
  GROUP BY 1, 2) sub
GROUP BY 1;

In the FROM clause you break down the JSON hierarchy with functions like jsonb_each and jsonb_to_recordset. As the last function's name already implies, each of these produces a set of records that you can work with just like you would with any other table and its columns. In the column selection list you select the required data and the appropriate aggregate functions json_agg and json_object_agg to piece the JSON result back together. For every level of hierarchy you need one aggregate function and therefore one level of sub-query.

How do I return an array of objects from jsonb?

1 Answers1