sql - Query from a table to get a JSON result

Question

I have a table

I'd like to get the JSON result like this:

[
   {
      "Type":"I", //If group name is empty display ‘I’ otherwise, display ‘G’
      "StartDate":"20/12/2020 8:00am", //This is the start time 
      "Additional":{   //Because it is non-group (GroupName is null), so Additional is an object
         "Info":"having food"
      }
   },
   {
      "Type":"G",  //Group by Date and Group Name.
      "GroupName":"School", //If record has the same group name, 
      "StartDate":"20/12/2020 9:00am", //!!! Display the first entry’s start date  
      "Additional":[ // It is an array due to Group Name being a real value, the items in array is the record whose StartDate has the same Date value.
         {
            "StartDate":"20/12/2020 9:00am", //Display the start date 
            "Info":"Take bus"
         },
         {
            "StartDate":"20/12/2020 9:30am",", //Display the start date
            "Info":"On the beach"
         }
      ]
   },
   {
      "Type":"G",
      "GroupName":"Class 1",
      "StartDate":"20/12/2020 11:00am",
      "Additional":[
         {
            "StartDate":"20/12/2020 11:00am",
            "Info":"Restaurant "
         },
         {
            "StartDate":"20/12/2020 13:00pm",
            "Info":"Walk on Lane"
         }
      ]
   },
   {
      "Type":"I",
      "StartDate":"20/12/2020 15:00pm",
      "Additional":{
         "Info":"In museum"
      }
   }
]

Would any one help me on this? Thank you so much.

I also attach the JSON data in this picture so the JSON format would be clearer.

There is a typo sorry, this is the new picture:

Answer 1

As per Microsoft SQL Server documentation, FOR JSON, will work best for you. It is supported on SQL Server 2016 and above.

SELECT CASE
           WHEN GroupName is null THEN 'I'
           ELSE 'G'
       END as Type
       ,GroupName
       ,StartDate .....
FROM table
FOR JSON AUTO;

https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15