Creating array of JSON objects from multiple columns

Question

I have the following dataset here

id	key	category	avg_time_1	avg_time_2
1	1	10	10	20
2	1	20	30	40
3	2	10	10	50
4	2	20	60	70

Sqlfiddle.

I want to create a query that the result will be as follow:

key	avg_time_1	avg_time_1
1	[{ "category": 10, "avg_time": 10},{ "category": 20, "avg_time": 20 }]	[{"category": 10, "avg_time": 20}, {"category": 20, "avg_time": 40}]
2	[{ "category": 10, "avg_time": 10},{ "category": 20, "avg_time": 60}]	[{"category": 10, "avg_time": 50}, {"category": 20, "avg_time": 70}]

The idea is just to re-present the values cols avg_time_1 and avg_time_2 in a different way, as jsonb values.

Answer 1

Use jsonb_build_object() to build prime objects and jsonb_agg() to aggregate them into a json array:

select 
    key, 
    jsonb_agg(jsonb_build_object('category', category, 'avg_time', avg_time_1)) as avg_time_1,
    jsonb_agg(jsonb_build_object('category', category, 'avg_time', avg_time_2)) as avg_time_2
from data_to_agg_json
group by key

 key |                              avg_time_1                              |                              avg_time_2                              
-----+----------------------------------------------------------------------+----------------------------------------------------------------------
   1 | [{"avg_time": 10, "category": 10}, {"avg_time": 30, "category": 20}] | [{"avg_time": 20, "category": 10}, {"avg_time": 40, "category": 20}]
   2 | [{"avg_time": 10, "category": 10}, {"avg_time": 60, "category": 20}] | [{"avg_time": 50, "category": 10}, {"avg_time": 70, "category": 20}]
(2 rows)    

Db<>Fiddle.