使用TSQL查询和更新 JSON 数据

JSON是一个非常流行的，用于数据交换的文本数据(textual data)格式，主要用于Web和移动应用程序中。JSON 使用“键/值对”(Key:Value pair)存储数据，能够表示嵌套键值对和数组两种复杂数据类型，JSON仅仅使用逗号(引用Key)和中括号(引用数组元素)，就能路由到指定的属性或成员，使用简单，功能强大。在SQL Server 2016版本中支持JSON格式，使用Unicode字符类型表示JSON数据，并能对JSON数据进行验证，查询和修改。推荐一款JSON验证和格式化的在线工具：json formatter。

SQL Server 提供了内置函数，用于查询和更新JSON数据，分析JSON文本，如图：

一，定义和验证JSON数据

使用nvarchar存储JSON文本数据，通过函数 ISJSON(expression) 验证JSON数据是否有效。

declare @json nvarchar(max)
set @json = 
N'{
    "info":{  
      "type":1,
      "address":{  
        "town":"bristol",
        "county":"avon",
        "country":"england"
      },
      "tags":["sport", "water polo"]
   },
   "type":"basic"
}'

select isjson(@json)

ISJSON 函数的格式是: ISJSON ( expression ) ，返回1，表示字符串是JSON数据；返回0，表示字符串不是JSON数据；返回NULL，表示 expression是NULL；

二，JSON 数据的PATH 表达式

Path 表达式分为两部分：Path Mode和Path，Path Mode是可选的(optional)，有两种模式：lax和strict。

1，Path Mode

在Path 表达式的开始，可以通过lax 或 strict 关键字显式声明Path Mode，如果不声明，默认的Path Mode是lax。在lax 模式下，如果path表达式出错，那么JSON函数返回NULL。在strict模式下，如果Path表达式出错，那么JSON函数抛出错误；

2，Path 表达式

Path是访问JSON数据的途径，有四种运算符：

• $：代表整个JSON 数据的内容；
• 逗号 . ：表示JSON对象的成员，也叫做，字段(Field)，或Key；
• 中括号 [] ：表示数组中的元素，元素的起始位置是0；
• Key Name：键的名字，通过Key Name引用对应的Value；如果Key Name中包含空格，$，逗号，中括号，使用双引号；

例如，有如下JSON 数据，通过Path表达式，能够路由到JSON的各个属性：

{ "people":  
  [  
    { "name": "John", "surname": "Doe" },  
    { "name": "Jane", "surname": null, "active": true }  
  ]  
} 

Path表达式查询的数据是：

• $：表示JSON的内容，是最外层大括号中的所有Item，本例是一个people数组，数组的下标是从0开始的；
• $.people[0]：表示people数组的第一元素：{ "name": "Jane", "surname": null, "active": true }
• $.people[0].name ：从people数组的第一个元素中，查询Key是Name的Item对应的数据，本例是John；
• $.people[1].surname：people数组中部存在surname 字段，由于该Path 表达式没有声明Path Mode，默认值是lax，当Path表达式出现错误时，返回NULL；

三，通过Path查询JSON数据

1，查询标量值(JSON_VALUE)

使用 JSON_VALUE(expression , path ) 函数，从JSON数据，根据Path 参数返回标量值，返回的数据是宽字符类型，最大值Nvarchar(4000)；如果必须返回大于nvarchar(4000)的数据，使用OpenJson行集函数。

declare @json nvarchar(max)
set @json = 
N'{
    "info":{  
      "type":1,
      "address":{  
        "town":"bristol",
        "county":"avon",
        "country":"england"
      },
      "tags":["sport", "water polo"]
   },
   "type":"basic"
}'

select
  json_value(@json, '