sql - 从表中查询以获取 JSON 结果

Question

我有一张桌子

我想得到这样的 JSON 结果：

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

有人会帮助我吗？非常感谢。

我还附上了这张图片中的 JSON 数据，这样 JSON 格式会更清晰。

有错别字抱歉，这是新图：

标签： sqljsonsql-server

Answer 1

根据 Microsoft SQL Server 文档，FOR JSON最适合您。它在 SQL Server 2016 及更高版本上受支持。

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