Convert JSONB Array to Columns

Question

I have a query that returns a single column containing jsonb objects. Here is an example of some returned rows (jsonb::text for legibility here):

[{"amount": 3, "consumable": "Mitsu 90mm ODEX Pilot Bit", "consumed_date": "2017-02-17"}]
[{"amount": 3, "consumable": "BOP Rubber", "consumed_date": "2017-02-17"}]
[{"amount": 13, "consumable": "NWJ Long Saver Sub Pin x Box", "consumed_date": "2017-02-18"}, 
    {"amount": 70, "consumable": "NWJ Long Saver Sub Pin x Box", "consumed_date": "2017-02-17"}]
[{"amount": 63, "consumable": "Methyl Hydrate - Per Litre", "consumed_date": "2017-02-17"}]
[{"amount": 6, "consumable": "Cyclone Discharge Hose Assembly", "consumed_date": "2017-02-17"}]

Give those example data, I would like a query to produce a table having the schema

consumable                      | 2017-02-17 | 2017-02-18
`````````````````````````````````````````````````````````
BOP Rubber                      | 3          | NULL
Cyclone Discharge Hose Assembly | 6          | NULL
Methyl Hydrate - Per Litre      | 63         | NULL
Mitsu 90mm ODEX Pilot Bit       | 3          | NULL
NWJ Long Saver Sub Pin x Box    | 70         | 13

It is not possible to add the tablefunc extension to this database instance.

Apologies for the formatting, I need to work on that. – Joebocop Feb 17 '17 at 23:04 — Joebocop, Feb 17 '17 at 23:04

score 3 · Accepted Answer · edited May 23 '17 at 10:29

Step 1.

You can aggregate amounts in a json object for each consumable. The jsons have consumed_dates as their keys:

with test(js) as (
values
    ('[{"amount": 3, "consumable": "Mitsu 90mm ODEX Pilot Bit", "consumed_date": "2017-02-17"}]'::jsonb),
    ('[{"amount": 3, "consumable": "BOP Rubber", "consumed_date": "2017-02-17"}]'),
    ('[{"amount": 13, "consumable": "NWJ Long Saver Sub Pin x Box", "consumed_date": "2017-02-18"}, {"amount": 70, "consumable": "NWJ Long Saver Sub Pin x Box", "consumed_date": "2017-02-17"}]'),
    ('[{"amount": 63, "consumable": "Methyl Hydrate - Per Litre", "consumed_date": "2017-02-17"}]'),
    ('[{"amount": 6, "consumable": "Cyclone Discharge Hose Assembly", "consumed_date": "2017-02-17"}]')
)

select consumable, jsonb_object_agg(consumed_date, amount) as amounts
from (
    select
        e->>'consumable' as consumable,
        (e->>'amount')::int as amount,
        e->>'consumed_date' as consumed_date
    from test,
    lateral jsonb_array_elements(js) e
    ) s
group by 1;

           consumable            |               amounts                
---------------------------------+--------------------------------------
 Mitsu 90mm ODEX Pilot Bit       | {"2017-02-17": 3}
 BOP Rubber                      | {"2017-02-17": 3}
 NWJ Long Saver Sub Pin x Box    | {"2017-02-17": 70, "2017-02-18": 13}
 Methyl Hydrate - Per Litre      | {"2017-02-17": 63}
 Cyclone Discharge Hose Assembly | {"2017-02-17": 6}
(5 rows)

Step 2.

Use the function create_jsonb_pivot_view() described in this post. You have to create a new table with the above data to use the function, e.g.:

drop table if exists my_new_table;
create table my_new_table as
    with test(js) as (
    --
    -- the above query
    ---
    group by 1;

select create_jsonb_pivot_view('my_new_table', 'consumable', 'amounts');

select * 
from my_new_table_view
order by 1;

           consumable            | 2017-02-17 | 2017-02-18 
---------------------------------+------------+------------
 BOP Rubber                      | 3          | 
 Cyclone Discharge Hose Assembly | 6          | 
 Methyl Hydrate - Per Litre      | 63         | 
 Mitsu 90mm ODEX Pilot Bit       | 3          | 
 NWJ Long Saver Sub Pin x Box    | 70         | 13
(5 rows)

Alternative solution

You can use this query to get results as close as possible to the desired format without the use of DDLs (assuming that the_data contains the data from the question):

with aux as (
    select
        e->>'consumable' as consumable,
        e->>'amount' as amount,
        e->>'consumed_date' as consumed_date
    from the_data,
    lateral jsonb_array_elements(js) e
)
select 
    null as consumable, 
    array_agg(distinct consumed_date order by consumed_date) as amounts
from aux
union all
select 
    consumable, 
    array_agg(amount order by consumed_date) as amounts
from (
    select distinct t1.consumed_date, t2.consumable
    from aux t1
    cross join aux t2
    ) s
left join aux t using(consumed_date, consumable)
group by 1
order by 1 nulls first;

           consumable            |         amounts         
---------------------------------+-------------------------
                                 | {2017-02-17,2017-02-18}
 BOP Rubber                      | {3,NULL}
 Cyclone Discharge Hose Assembly | {6,NULL}
 Methyl Hydrate - Per Litre      | {63,NULL}
 Mitsu 90mm ODEX Pilot Bit       | {3,NULL}
 NWJ Long Saver Sub Pin x Box    | {70,13}
(6 rows)

That's a thing of beauty, thank you. My unfortunate situation is that I don't have DDL rights on this server. Is there any way to accomplish this with DML only? — Joebocop, Feb 18 '17 at 19:50
It is rather impossible without the possibility of performing dynamic SQL, so you should have an access to create custom functions at least. However, the result obtained in step 1 makes it easier to implement the relevant transformations in your client application. — klin, Feb 18 '17 at 20:55

Convert JSONB Array to Columns

1 Answers1

Step 1.

Step 2.

Alternative solution