使用 OPENJSON 及 FOR JSON 剖析及轉換 JSON 資料 (MS SQL)

適用範圍：SQL Server 2016 (13.x) 及更新版本

 基本使用

 JSON -> TABLE

先準備一份 JSON 格式的資料

DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]' 

使用 OPENJSON 解析成兩筆資料

使用 WITH 定義每個屬性的對應欄位，將每筆資料轉成Table格式

就可以得到一份資料表結構的資料

 TABLE -> JSON 

借用上面語法，加上 FOR JSON 就可以再轉換成 JSON 格式

貼到 JSON Editor Online 驗證一下

FOR JSON 可以再搭配 AUTO 及 PATH 使用，兩者差異可參考官方文件有詳細說明

• 若要保有對 JSON 輸出格式的完整控制權，請使用 FOR JSON PATH。 您可以建立包裝函式物件和巢狀複雜屬性。
• 若要根據 SELECT 陳述式的結構自動格式化 JSON 輸出，請使用 FOR JSON AUTO

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

到這邊，我們知道了可以使用 MSSQL 在Table 與 JSON 之間做轉換

接下來才是重頭戲!

前陣子剛好在開發上需要將資料組成這種格式 (為了打api)

Id、No都是單純的字串，但 AddLabels 放的會是一個陣列，似乎是沒辦法直接套用語法就可以

以下列出我嘗試的過程與結果

1. 先準備測試資料 (需要分兩個Table)

#tmpEmp 放員工，#tmpData放員工各自有的標籤

SELECT 'No1' AS No
INTO #tmpEmp
UNION ALL
SELECT 'No2' AS No
UNION ALL
SELECT 'No3' AS No


SELECT 'No1' AS No, 'Api/修改' AS Labels
INTO #tmpData
UNION ALL
SELECT 'No1' AS No, 'Api/修改1' AS Labels
UNION ALL
SELECT 'No2' AS No, 'Api/修改2' AS Labels
UNION ALL
SELECT 'No3' AS No, 'Api/修改' AS Labels
UNION ALL
SELECT 'No3' AS No, 'Api/修改3' AS Labels

SELECT * FROM #tmpEmp
SELECT * FROM #tmpData 

2. 一開始先嘗試直接使用 FOR JSON AUTO

將出來的結果丟到 JSON Editor Online 剖析看一下，會發現結果出來AddLabels 這邊不如預期

3. 後來參考到此篇文章方式，先將陣列的資料串起來，再搭配 JSON_QUERY 使用

https://stackoverflow.com/questions/37708638/sql-to-json-array-of-objects-to-array-of-values-in-sql-2016

SELECT '' AS Id, A.No AS No, 
	JSON_QUERY(
		'[' + STUFF(
		(SELECT ',' + '"' + Labels + '"' FROM #tmpData AS AA 
		WHERE AA.No = A.No 
		FOR XML PATH ('')), 1, 1, '') + ']'
	) AS [AddLabels] 
FROM #tmpEmp AS A 
FOR JSON AUTO
 

產出跟 api data 規定格式一致的 json 字串~~~ 大功告成!