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);
-- 增加属性 tempProp1
SET @jsonInfo = JSON_MODIFY(@jsonInfo, 'tempProp1', CONVERT(BIT, 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