JSON with roots for every selected day

Question

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 1

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY key, [date] DATETIME, title VARCHAR(20), start_time DATETIME, end_time DATETIME);
INSERT INTO @tbl (date, title, start_time, end_time) VALUES
('2022-01-10T00:00:00', 'Coloring','2022-01-10T12:00:00','2022-01-10T13:00:00'),
('2021-12-28T00:00:00', 'Coloring','2021-12-27T15:20:00','2021-12-27T16:00:00'),
('2021-12-28T00:00:00', 'Coloring','2021-12-27T12:20:00','2021-12-27T14:00:00');
-- DDL and sample data population, end

SELECT * FROM @tbl;

SELECT CONCAT(
    N'{',
    STUFF(
      (
        SELECT CONCAT(N',"', CAST(k.[date]AS DATE), '":', c.[Json])
        FROM @tbl AS k
        CROSS APPLY (
            SELECT [date], title, start_time, end_time
            FROM @tbl
            WHERE [date] = k.[date]
            FOR JSON PATH
        ) c([Json])
        GROUP BY [date], c.[Json]
        ORDER BY [date] DESC
        FOR XML PATH('')
        ), 1, 1, N''
   ),  
   N'}'
) 
AS JsonOutput;

Output

{
    "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"
        }
    ]
}

Answer 2

In modern versions of SQL Server, you can use STRING_AGG for this, once to aggregate per date, then again to aggregate the whole thing.

You also get the benefit of only querying the table once (the APPLY refers back to the outer table only).

Note that if you are using arbitrary text as the keys, you should use STRING_ESCAPE to escape them

SELECT
  JsonOutput = N'{' + STRING_AGG(N'"' + CAST(t.date AS nvarchar(30)) + N'":' + t.[Json], N',') WITHIN GROUP (ORDER BY [date] DESC) + N'}'
FROM (
    SELECT
      date = CAST(t.date AS date),
      Json = STRING_AGG(N'[' + c.Json + N']', ',') WITHIN GROUP (ORDER BY [date] DESC)
    FROM @tbl t
    CROSS APPLY (
        SELECT t.date, t.title, t.start_time, t.end_time
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) c(Json)
    GROUP BY CAST(t.date AS date)
) t;

db<>fiddle

Answer 3

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