Structure the JSON object using group by in Postgres SQL

Question

I'm trying to create structure using the Postgres SQL query. The structure is define to search faster for some advance functionalities of the application so that's why I couldn't change this structure and I guess this is the most viable structure for now.

Table: provider_schedule

|_id|user_id|day|start_time|end_time|hours_format|
|---|-------|---|----------|--------|------------|
|13 |87     |0  |09:00:00  |12:00:00|1           |
|14 |87     |0  |15:00:00  |20:00:00|1           |
|15 |87     |1  |09:00:00  |12:00:00|1           |
|16 |87     |1  |15:00:00  |20:00:00|1           |
|17 |87     |2  |15:00:00  |20:00:00|1           |
|18 |87     |2  |09:00:00  |12:00:00|1           |

Query:

SELECT day, jsonb_object_agg(start_time, end_time) as time_array, min(hours_format) as hours_format 
FROM provider_schedule 
WHERE user_id = 87 
GROUP BY day;

Current result:

[{
    day: 0,
    time_array: {
      '09:00:00': '12:00:00',
      '15:00:00': '20:00:00'
    },
    hours_format: 1
  },
  {
    day: 1,
    time_array: {
      '09:00:00': '12:00:00',
      '15:00:00': '20:00:00'
    },
    hours_format: 1
  },
  {
    day: 2,
    time_array: {
      '09:00:00': '12:00:00',
      '15:00:00': '20:00:00'
    },
    hours_format: 1
  }
]

Expected result:

[{
    day: 0,
    time_array: [{
        start_time: '09:00:00',
        end_time: '12:00:00'
      },
      {
        start_time: '15:00:00',
        end_time: '20:00:00'
      }
    ],
    hours_format: 1
  },
  {
    day: 1,
    time_array: [{
        {
          start_time: '09:00:00',
          end_time: '12:00:00'
        },
        {
          start_time: '15:00:00',
          end_time: '20:00:00'
        }]
      hours_format: 1
    },
    {
      day: 2,
      {
        start_time: '09:00:00',
        end_time: '12:00:00'
      },
      {
        start_time: '15:00:00',
        end_time: '20:00:00'
      }],
    hours_format: 1
  }
]

I want this expected structure from query. I don't want to use JavaScript to create this structure after query result. Thank you in advance.

Answer 1

You need to levels of aggregation as you can't nest aggregate functions directly - which is what you need to get an array for the whole result and rows for one day as an array as well.

select jsonb_agg(single_day)
from (
  select jsonb_build_object('day', day, 
                            'hours_format', min(hours_format), 
                            'time_array', jsonb_agg(jsonb_build_object('start_time', start_time, 'end_time', end_time) order by start_time)) as single_day
  from provider_schedule
  where user_id = 87
  group by day
) t  

Online example