在Sql Server 2016中使用For Json子句把資料作為json格式匯出

標籤：

原文地址：http://www.ncloud.hk/%E6%8A%80%E6%9C%AF%E5%88%86%E4%BA%AB/export-query-result-as-json-format-in-sql-server-2016/

使用for json子句把查詢結果作為json字串匯出，將作為sql server 2016中首先可用的一個特性。如果你熟悉for xml子句，那麼將很容易理解for json：

select ccolumn, expression, column as alias    from table1, table2, table3    for json [auto | path]

如果你把for json子句添加到T-SQL Select查詢語句的最後，SQL Server將會把結果格式化為JSON字串之後在返回到用戶端。每一行資料將會格式化為一個json對象，每一個資料欄位將會成為行對象的值，列名或者列的別名會作為行對象的鍵。我們有兩種類型的for json子句：

• FOR JSON Path，通過列名或者列別名來定義JSON對象的階層，列別名中可以包含“.”，JSON的成員階層將會與別名中的階層保持一致。
  這個特性非常類似於早期SQL Server版本中的For Xml Path子句，可以使用斜線來定義xml的階層。
• FOR JSON Auto，自動按照查詢語句中使用的表結構來建立嵌套的JSON子數組，類似於For Xml Auto特性。

如果你用過PostgreSQL中涉及到JSON的函數和操作符，你會注意到，FOR JSON子句類等價於PostgreSQL中的JSON建立函數比如row_to_json或json_object。FOR JSON子句的主要目的是根據JSON規範把變數、列格式化為JSON對象。比如：

set @json = (select 1 as firstKey, getdate() as dateKey, @someVar as thirdKey for json path)-- result is : {"firstKey": 1, "dateKey": "2016-06-15 11:35:21", "thirdKey": "Content of variable"}

FOR JSON子句主要應用情境：

• 把需要返回給用戶端的一組對象序列化為JSON。想象一下，在你建立JSON Web服務的時候，需要提供供應商資訊及其產品資訊（比如在OData服務中使用$extend選項）。你可能會查詢供應商列表，把每個供應商資訊格式化為JSON對象並通過額外查詢來獲得這個供應商的產品列表，將其轉化為JSON對象數組附加到供應商對象。其他方案可能會通過連結查詢來獲得供應商和產品資訊列表，使用用戶端代碼來格式化為JSON對象（若使用Entity Framework將可能產生額外查詢）。使用for json子句，你可以串連這兩個表進行查詢，添加你想要的首碼（定義JSON階層），在資料庫層完成JSON格式化工作。
• 在一對多的父子表關係情境，你不想建立子表，而是想把子表的記錄以JSON數組的格式儲存作為父表的一列。比如你不想把SalesOrderHeader和SalesOrderDetails資料分成兩個表來儲存，你可以把每個訂單的多個商品詳情格式化為JSON數組儲存到SalesOrderHeader表中的一列。

在Sql Server 2016中使用For Json子句把資料作為json格式匯出