前言
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_MODIFYJSON_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
OPENJSONOPENJSON 函式能將 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 PATH2.
[Database][SQL Server] SQL Server 2016 新語法 - JSON_VALUE 與 JSON_QUERY3.
[Database][SQL Server] SQL Server 2016 新語法 - DROP IF EXISTS