Question

Source: 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

Answer

Group day table, which is ordered by id, by the date part of the date field, and use the date part as field headers. Values of each field are table sequences of corresponding groups, and then we transform them into JSON format. SQL can only assemble the JSON string manually according to the specified format. The statement is lengthy and difficult to read. A general alternative is to fetch the original data out of the database and process it in Python or SPL. SPL, the open-source Java package, is easier to be integrated into a Java program and generate simple code. It does the task with only two lines of code:

	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())

 

View SPL source code.