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:

For MySQL, you can see a simple way https://stackoverflow.com/a/41759147/2841508 — Kashif Sohail, Dec 24 '20 at 06:45
Have you take a look at `FOR JSON` https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15 ? — Squirrel, Dec 24 '20 at 06:50
@KashifSohail The newest current one, could write the sql how I can get the above result? Thank you so much if you can help me. — Jeremy Lee, Dec 24 '20 at 06:54

score 0 · Accepted Answer · edited Dec 24 '20 at 08:39

0

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://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15

edited Dec 24 '20 at 08:39

Dale K

25,246
15
42
71

answered Dec 24 '20 at 07:02

Kashif Sohail

184
5

Hi, I really need to complete this query. Can I add you as friend so I can ask you how to achieve it step by step? – Jeremy Lee Dec 24 '20 at 07:11
I'm having the problem on grouping the items actually, because I can't figure it out using group by. I have no idea then. – Jeremy Lee Dec 24 '20 at 07:13
sure, you can add – Kashif Sohail Dec 24 '20 at 07:16

score 0 · Answer 2 · answered Dec 24 '20 at 07:09

0

When you form a JSON structure. Better to use with "INCLUDE_NULL_VALUES". So that it will have NULL for attribute If the value is missing.

SELECT Column1, Column2,... FROM Table FOR JSON AUTO, INCLUDE_NULL_VALUES

answered Dec 24 '20 at 07:09

SQLServerBuddy

161
6

Query from a table to get a JSON result

2 Answers2