DB研討會 : [轉貼]T-SQL OPENJSON

前言

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


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

介紹

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