SqlServer 操作 JSON

Intro

Sql Server 從 2016 開始支持了一些 json 操作，最近的項目里也是好多地方直接用欄位直接存成了 json ，需要瞭解一下怎麼在 Sql Server 中操作 JSON.
JSON支持適用於 SqlServer 2016 及以上版本 和 Azure SQL Database。

SqlServer 中內置了一些 JSON 相關的方法：
可以判斷一段字元串是否是標準的 json（ISJSON）
可以直接查詢數據成 json 格式(FOR JSON PATH) 類似於之前的查詢一個 xml (FOR XML PATH)，
查詢一個 json 對象的值(JSON_VALUE)
查詢一個 json 數組值
更新一段JSON的內容，修改 JSON 對象里的屬性值，刪除 JSON 對象里的某一個屬性，增加屬性
解析一段 json 內容 (OPENJSON)

JSON 操作

JSON 存儲

資料庫里 JSON 存儲一般用 NVARCHAR(MAX) 類型來保存，如果一定是 JSON 形式的數據可以設置一個約束，可以通過 ISJSON 來給欄位加約束，詳情參考

JSON 屬性加索引

要給 JSON 對象的某個屬性加欄位時，需要增加一個虛擬的列，然後在這個列中建立一個索引。

ALTER TABLE Sales.SalesOrderHeader
ADD vCustomerName AS JSON_VALUE(Info,'$.Customer.Name')

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)

JSON 基本操作

{
    "name": "小明",
    "info": {
        "address": {
            "province": "河南省",
            "city": "鄭州市",
            "district": "鄭東新區"
        },
        "hobbies": [
            "籃球",
            "足球",
            "乒乓球"
        ]
    }
}

-- 查詢某一屬性值
SET @name = JSON_VALUE(@jsonInfo, '$.name');
SET @city = JSON_VALUE(@jsonInfo, '$.info.address.city');

-- 查詢數組
SET @hobbies = JSON_QUERY(@jsonInfo, '$.info.hobbies');

-- 增加屬性 tempProp

SET @jsonInfo = JSON_MODIFY(@jsonInfo, 'tempProp', 1);

-- 刪除屬性 tempProp
SET @jsonInfo = JSON_MODIFY(@jsonInfo, 'tempProp', null);

Reference

• https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017
• https://docs.microsoft.com/en-us/sql/t-sql/functions/isjson-transact-sql?view=sql-server-2017
• https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-2017
• https://docs.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-2017
• https://docs.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?view=sql-server-2017
• https://docs.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server?view=sql-server-2017