JSON with roots for every selected day-CodePudding

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

CodePudding user response：

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