前言

Microsoft SQL Server 2016 版本開始對於 JSON 資料型態提供多種語法上的支援，在這一篇我們將簡單介紹 ISJSON、 JSON_MODIFY 與 OPENJSON。這些語法的作用為：

 ISJSON
 用於驗證資料格式是否為 JSON 格式，是回傳 true；否則回傳 false
 JSON_MODIFY
 用於修改 JSON 資料內容
 OPENJSON
 對於 JSON 資料進行解析，拆解成資料表格是顯示 Key、Value 與類型；配合 WITH 語法可接 JSON 轉換成資料表結果。

本篇文章若有錯誤或任何建議，請各位先進不吝指教。



介紹

ISJSON
這個簡單的函式可以用來驗證帶入的資料內容是否為正確的 JSON 格式，避免錯誤的資料造成程式錯誤。我們透過範例簡單說明 ISJSON 用法：
Step 1. 首先我們先建立範例資料表與資料，其中有幾筆資料的 Note 欄位存放正確 JSON 資料：




Step 2.我們透過下列語法，找出 Note 中格式為 JSON 的資料 
    SELECT [Id]
          ,[Name]
          ,[Salary]
          ,[Note]
      FROM [test].[dbo].[Employee] 
      WHERE ISJSON(Note) > 0

Step 3. 結果





JSON_MODIFY
JSON_MODIFY 可對於 JSON 資料進行操作，修改資料內容，下面的範例簡單說明如何使用JSON_MODIFY。
Step 1. 我們先建立一個 NVARCHAR(MAX) 資料，寫入 JSON 資料： 
DECLARE @jsonInfo NVARCHAR(MAX);
SET @jsonInfo = N'{
    "Id":1,
    "Name":"Duran",
    "Salary":1000,
    "Role":[
      "developer",
      "QA"
     ]
}'

print @jsonInfo





Step 2.我們透過下列語法，嘗試將 Role JSON Array 第一筆資料改成 Designer
關於 JSON PATH 表示方法，請參考這裡
DECLARE @jsonInfo NVARCHAR(MAX);

SET @jsonInfo = N'{
    "Id":1,
    "Name":"Duran",
    "Salary":1000,
    "Role":[
      "developer",
      "QA"
     ]
}'

DECLARE @info NVARCHAR(MAX);
SET @info = JSON_MODIFY(@jsonInfo, '$.Role[0]', 'Designer')    
print @info






OPENJSON
OPENJSON 函式能將 JSON 資料轉換成資料表方式呈現。首先我們先建立以下字串內容，並透過 OPENJSON 印出結果： 
DECLARE @jsonInfo NVARCHAR(MAX);

SET @jsonInfo = N'{
    "Id":1,
    "Name":"Duran",
    "Salary":1000,
    "Role":[
      "developer",
      "QA"
     ]
}'

SELECT *
FROM OPENJSON(@jsonInfo)



您能看見 OPENJSON 將 key-value 分離出來，依據不同資料型態進行分類(文字1、數字2...依此類推)。

接下來，我們透過 with 語法，將資料轉換成資料表格式呈現。其中若不想進行轉換，可以透過AS JSON直接儲存JSON資料，但記得其格式一定要為 nvarchar(MAX)。
SELECT *
FROM OPENJSON(@jsonInfo)
WITH (
    Id INT '$.Id',
    Name nvarchar(20) '$.Name',
    Salary INT '$.Salary',
    Role  nvarchar(MAX)  AS JSON 
)







參考資料

1. OPENJSON (TRANSACT-SQL) - Microsoft docs
2. 使用內建函數，驗證、查詢以及變更 JSON 資料 (SQL Server) - Microsoft docs

JSON PATH 
下列為 JSON 範例，表格中為 JSON 表示的資料內容

{
    "Id":1,
    "Name":"Duran",
    "Salary":1000,
    "Role":[
      "developer",
      "QA"
     ]
}

相關文章

1.[Database][SQL Server] SQL Server 2016 新語法 - FOR JSON AUTO 與 FOR JSON PATH
2.[Database][SQL Server] SQL Server 2016 新語法 - JSON_VALUE 與 JSON_QUERY
3.[Database][SQL Server] SQL Server 2016 新語法 - DROP IF EXISTS