使用計算列索引 JSON 屬性

在 SQL Server 中儲存 JSON 文件時，我們需要能夠有效地過濾和排序 JSON 文件屬性的查詢結果。

CREATE TABLE JsonTable
(
    id int identity primary key,
    jsonInfo nvarchar(max),
    CONSTRAINT [Content should be formatted as JSON]
    CHECK (ISJSON(jsonInfo)>0)
)

INSERT INTO JsonTable
VALUES(N'{"Name":"John","Age":23}'),
(N'{"Name":"Jane","Age":31}'),
(N'{"Name":"Bob","Age":37}'),
(N'{"Name":"Adam","Age":65}')
GO

給定上表如果我們想要找到名稱為’Adam’的行，我們將執行以下查詢。

SELECT * 
FROM JsonTable Where 
JSON_VALUE(jsonInfo, '$.Name') = 'Adam'

但是，這將需要 SQL 伺服器執行一個完整的表，在大表上不是很有效。

為了加快速度，我們想新增一個索引，但是我們不能直接引用 JSON 文件中的屬性。解決方案是在 JSON 路徑 $.Name 上新增計算列，然後在計算列上新增索引。

ALTER TABLE JsonTable
ADD vName as JSON_VALUE(jsonInfo, '$.Name')

CREATE INDEX idx_name
ON JsonTable(vName)

現在，當我們執行相同的查詢時，SQL Server 使用索引來尋找非聚集索引並找到滿足指定條件的行，而不是全表掃描。

注意：要使 SQL 伺服器使用索引，必須使用計劃在查詢中使用的相同表示式建立計算列 - 在此示例中為 JSON_VALUE(jsonInfo, '$.Name')，但是你也可以使用計算列名稱 vName