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