SQL 2019 之 JSON 指令試用紀錄

自 SQL Server 2016 開始支援 JSON 指令，決定花點時間正式試用看看。

參考文件

JSON 函式 (Transact-SQL) - SQL ServerMicrosoftLearn

在 SQL Server 中使用 JSON 數據 - SQL ServerMicrosoftLearn

Index JSON Data - SQL ServerMicrosoftLearn

computed_column_definition (Transact-SQL) - SQL ServerMicrosoftLearn

淺談計算欄位(Computed Column)的PERSISTED引數 | Rock的SQL筆記本 - 點部落Rock的SQL筆記本

預計使用情境

當然是存表單這種典型的半結構化資料體。

表單CRUD

增刪查改是基本。

表單索引

因為表單有大量查詢需求故一定要上索引。索引目標不可能是整體JSON，只能是其中的單號或日期欄位。

變更表單狀態

表單基本狀態最少有：DRAFT(APPLY) → COMMIT → APPROVED

在更新狀態時是否不必全部更新，只更新其中兩、三個欄位，比如：狀態、異動日期、異動人員。

試用紀錄

一開始就要決定好表單的資料結構。

試用指令紀錄

建立表單檔，以 JSON 格式儲存。

-- 建立表單檔，以 JSON 格式儲存。
-- 其中 FormContent 存入 JSON，其他欄位都是 COMPUTED 計算出來，
-- 其中 vFormNo 表單編號要上 Index，故一定要加入 PERSISTED 屬性。
CREATE TABLE [dbo].[MyJson](
	[FormContent] [nvarchar](max) NULL, 
	[vFormNo]  AS (json_value([FormContent],'$.FormNo')) PERSISTED, -- 注意：
	[vDescription]  AS (json_value([FormContent],'$.Description')),
	[vStatus]  AS (json_value([FormContent],'$.Status'))
)
GO

-- 也可以為 COMPUTED 欄位建立索引。記得要先標記屬性為 PERSISTED。
CREATE UNIQUE CLUSTERED INDEX [idx_MyJson_FormNo] ON [dbo].[MyJson]
(
	[vFormNo] DESC
)
GO

也能之後再加入新的 Computed Field

-- 加入新的 Computed Field
ALTER TABLE [MyJson] ADD [vFormNo] AS (json_value([FormContent],'$.FormNo')) PERSISTED; 
ALTER TABLE [MyJson] ADD [vStatus] AS (json_value([FormContent],'$.Status')); 

ISJSON(expression) 檢查是否符合 JSON 格式，若不符合所有 JSON 操作指令都會失靈。

SELECT ISJSON(FormContent), FormContent
  FROM [MyLabDB].[dbo].[MyJson]
  WHERE vDescription LIKE '%六號%'

新增一筆表單資料

-- 新增一筆 JSON 表單
DECLARE @jsonInfo NVARCHAR(MAX) = N'{
  "FormNo": "F2308006",
  "Description": "我是測試表單六號",
  "Apply" : {
    "AppUnit": "DevCenter",
    "AppStaff": "Rely",
    "AppDate": "2023-08-29"	
  },
  "Approve": {
    "ApvStaff": null,
	"ApvDate": null
  },
  "FormData": {
    "DataField01": "這一段放置表單申請內容"
  },
  "Status": "APPLY"
}';
IF ISJSON(@jsonInfo) = 1 -- INSERT 前先確認是否符合 JSON 格式。 
  INSERT INTO [MyJson] (FormContent) VAlUES (@jsonInfo);
ELSE
  THROW 50000, '不符合JSON格式不可存檔！', 1

刪除一筆，跟一般 SQL 刪除指令一樣。

-- 刪除一筆
DELETE FROM [MyJson] WHERE vFormNo = 'F2308006'

JSON_VALUE(expression,path) 從 JSON 字串擷取純量值。注意：此指令只能取純量值。

-- 取回 JSON value
SELECT [Status] = JSON_VALUE(FormContent,'$.Status')
     , vStatus -- 也能透過前面設好的COMPUTED欄位取值。
  FROM [MyLabDB].[dbo].[MyJson]
  WHERE vFormNo = 'F2308006'

JSON_MODIFY(expression,path,newValue) 從 JSON 字串更新純量值。

-- 直接更新 JOSN 內部數值
-- 註：若在 JSON_MODIFY 時值給 NULL 則效果等同移除該JSON node。
UPDATE MyJson
  SET FormContent = JSON_MODIFY(FormContent,'$.Status','COMMIT')
  WHERE vFormNo = 'F2308006'

更新 JOSN 多個數值

-- 更新 JOSN 多個數值
-- 串接 JSON_MODIFY 一次更新多個數值。
-- 註：JSON_MODIFY 只能更新『純數值』。不能更新『物件』『陣列』。
UPDATE MyJson
  SET FormContent = JSON_MODIFY(JSON_MODIFY(FormContent
    ,'$.Approve.ApvStaff','梅莉')
    ,'$.Approve.ApvDate','2023-08-31')
  WHERE vFormNo = 'F2308006'

JSON_QUERY(expression,path) 從 JSON 字串擷取物件或陣列。注意：此指令無法取純量值。

-- 取回 JSON section
SELECT ApplyInfo = JSON_QUERY(FormContent,'$.Approve')
  FROM [MyLabDB].[dbo].[MyJson]
  WHERE vDescription LIKE '%六號%'

---

搭配 CROSS APPLY OPENSJON 交叉查詢 JSON string array。

補充 on 2025-09-12

目的：設計一欄伉 RoleList 放員工角色清單。 下一SQL查詢所有 "Sales" 名單。

RoleList 的格式為 JSON string array。

SELECT [SalesId] = [UserId]
 ,[SalesName] = [Nickname]
 ,[Roles].[Role]
 FROM dbo.Staff S
 CROSS APPLY OPENJSON(S.RoleList) WITH ([Role] NVARCHAR(50) '$') AS Roles
 WHERE Roles.[Role] = 'Sales'
  AND [Enable] = 'Y';

附上測試資料與查詢結果

---

其他常用指令

• OPENJSON --- 把 JSON Array 轉成 DataTable。

• FOR JSON PATH --- 把 DataTable 轉成 JSON。

在 SQL Server 2022 以後才支援的JSON 指令

• JSON_PATH_EXISTS

• JSON_OBJECT

• JSON_ARRAY

(EOF)