實作半結構化表單資料表 in SQL Server

SQL Server：實作半結構化表單資料表。JSON, , Computed Columns.

引言

在 SQL Server 以儲存半結構化表單資料。有賴於軟體技術的進步這件事變得容易多了。

自 SQL Server 2016 開始支援 JSON 再搭配俱 PERSISTED 屬性的 Computed Column 就變得容易許多。

第一步：設計表單結構

直接以範例說明。實作時舉一反生。

{
  "FormNo": "F2308003",
  "Description": "我是測試表單三號",
  "Apply" : {
    "AppUnit": "DevCenter",
    "AppStaff": "Rely",
    "AppDate": "2023-08-29"	
  },
  "Approve": {
    "ApvStaff": null,
	"ApvDate": null
  },
  "FormData": {
    "DataField01": "這一段放置表單申請內容"
    ...
  },
  "Status": "APPLY"
}

其中表單編號、表單狀態等欄位要可以上索引，以加速查詢。

將以 JSON 存放完整的表單資料，附件除外。

欄位索引實作以 Computed Column 並設定 PERSISTED 屬性就能完成。

請注意：沒有 PERSISTED 屬性的 Computed Column 是無法上索引的。

第二步：設計 Data Table with JSON and Computed Column

以此例設計的 DataTable 指令：

-- 建立半結構化表單資料表，
-- 並把表單內容俱代表性資訊以 computed column 拉出外面。
CREATE TABLE [dbo].[MyJsonForm](
  [FormContent] [nvarchar](max) NULL,
  [vFormNo]  AS (json_value([FormContent],'$.FormNo')) PERSISTED, -- 表單編號上索引
  [vDescription]  AS (json_value([FormContent],'$.Description')),
  [vStatus]  AS (json_value([FormContent],'$.Status'))
)
GO

-- 為表單編號上索引
CREATE UNIQUE CLUSTERED INDEX [idx_MyJsonForm_FormNo] ON [dbo].[MyJsonForm]
(
  [vFormNo] DESC
)
GO