SqlServer 操作 JSON

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