[Database][SQL Server] SQL Server 2016 新語法 - ISJSON 、 JSON_MODIFY 與 OPENJSON

熱門文章 (Popular Post)

Posted by : Duran Hsieh 2018-01-24

前言

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

 用於驗證資料格式是否為 JSON 格式,是回傳 true;否則回傳 false
 用於修改 JSON 資料內容
 對於 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)

使用者上傳的圖片:image.png

您能看見 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


Leave a Reply

Subscribe to Posts | Subscribe to Comments

- Copyright © Duran Hsieh @ Duran 的技術冶煉廠 - Date A Live - Powered by Blogger - Designed by Johanes Djogan -