FredGol | Real Estate

Voice For Property Investing

JSON with roots for every selected day

I am struggling with the problem with nesting root for every day (it’s an element of my table). I’d like to get nested Key: value pair of day from table Day.

Here is my result:

[
   {
      "date":"2022-01-10T00:00:00",
      "title":"Coloring",
      "start_time":"2022-01-10T12:00:00",
      "end_time":"2022-01-10T13:00:00"
   },
   {
      "date":"2021-12-28T00:00:00",
      "title":"Coloring",
      "start_time":"2021-12-27T15:20:00",
      "end_time":"2021-12-27T16:00:00"
   },
   {
      "date":"2021-12-28T00:00:00",
      "title":"Coloring",
      "start_time":"2021-12-27T12:20:00",
      "end_time":"2021-12-27T14:00:00"
   }
]

expected result below:

{
   "2022-01-10":[
      {
         "date":"2022-01-10T00:00:00",
         "title":"Coloring",
         "start_time":"2022-01-10T12:00:00",
         "end_time":"2022-01-10T13:00:00"
      }
   ],
   "2021-12-28":[
      {
         "date":"2021-12-28T00:00:00",
         "title":"Coloring",
         "start_time":"2021-12-27T15:20:00",
         "end_time":"2021-12-27T16:00:00"
      },
      {
         "date":"2021-12-28T00:00:00",
         "title":"Coloring",
         "start_time":"2021-12-27T12:20:00",
         "end_time":"2021-12-27T14:00:00"
      }
   ]
}

day table:

id  date

0   2021-12-01 00:00:00.0000000
1   2021-12-02 00:00:00.0000000
2   2021-12-03 00:00:00.0000000
... ...

Here is my Event Table:

id  title       start_time                  end_time                 day_of_timetable  service_id
0   Coloring    2022-01-10 12:00:00.0000000 2022-01-10 13:00:00.0000000 0   0
1   Coloring    2021-12-27 15:20:00.0000000 2021-12-27 16:00:00.0000000 1   0
2   Coloring    2021-12-27 12:20:00.0000000 2021-12-27 14:00:00.0000000 1   0

Here is my day_of_timetable table:

id  day_id  end_user_id
0   40      1
1   27      1

Here is my code

    select date, e.title, e.start_time, e.end_time,  e.day_of_timetable_id
    from day 
    join day_of_timetable dot on day.id = dot.day_id
    join end_user eu on dot.end_user_id = eu.id
    join event e on dot.id= e.day_of_timetable_id
    where eu.id = 1 for json path