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.

What if the two rows for a day have a different hours_format? — user330315
– user330315, Commented Apr 5, 2022 at 5:56
@a_horse_with_no_name, it won't have different hour format. All column of same row will be in same hour format. — Jay Bhajiyawala
– Jay Bhajiyawala, Commented Apr 5, 2022 at 5:58

user330315 · Accepted Answer · 2022-04-05 06:08:45Z

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

Collectives™ on Stack Overflow

Structure the JSON object using group by in Postgres SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related