JSON with roots for every selected day
问题
https://stackoverflow.com/questions/70498946/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
解答
对id有序的数据，按日期字段的日期部分分组，将日期字段的日期部分作为表头，字段值是对应的组内序表，转成json格式即可。SQL只能按要求的格式，将json串手动拼出，写出来的SQL繁琐且难以阅读。通常的办法是读出来用Python或SPL来做， SPL（一种 Java 的开源包）更容易被Java应用集成，代码也更简单一点，只要两句：
A |
|
1 |
=MSSQL.query("select date,title,start_time,end_time from json order by id") |
2 |
=json(transpose(A1.group@o(date(date)).run(~=[date(date)]|[~])).export().import@t()) |
English version