SQL Server解析Json格式数据

主要介绍5个函数：

1. openJson:打开Json字符串
2. IsJson:判断一个字符串是不是合法的Json格式。是返回1，否返回0，null返回null。
3. Json_Value:从Json字符串中提取值。
4. Json_Query:Json字符串中提取对象或数组。
5. Json_Modify:更新Json字符串中的属性值，并返回更新的Json字符串。

开始之前：创建示例数据

declare @json_str varchar(max)
set @json_str='[
  {"key":3,"value1":10,"value2":[{"vv21":13,"vv22":103}],"value3":null},
  {"key":4,"value1":15,"value2":[{"vv21":13,"vv22":103}],"value3":"10"},
  {"key":7,"value1":20,"value2":[{"vv21":13,"vv22":103}],"value3":"15"}
]'

1. IsJson:判断是不是合法的Json格式

用法：IsJson(@express)

SELECT IsJson(@json_str) as isjson

2. Json_Value:从Json字符串中提取值

用法：Json_Value (value,’$.column’) from openjson(@express)

select JSON_VALUE(value,'$.key') as kk,
JSON_VALUE(value,'$.value1') as value1,
JSON_value(value,'$.value2') as value2,
JSON_QUERY(value,'$.value2') as value2_query,
JSON_VALUE(value,'$.value3') as value3
from openJson(@json_str) -- include_null_values

注：

1. include_null_values 表示当表达式中为null,则输出null，默认会输出null。
2. 当json_value获取的值是一个数组时，会返回null（如上value2),此时应该使用json_query进行解析。
   

3. Json_Query:从Json字符串中提取对象或数组

用法：Json_Query(value,’$.object’) from openjson(@express)

参见2中的示例

4. Json_Modify:更新Json字符串中的属性值

用法：json_Modify(value,’$.column’,newValue) from openjson(@express)

如将value1中的值都替换为10086

进阶用法:多层复杂的Json解析

将value2中嵌套的数组字典进行解析

select JSON_VALUE(value,'$.key') as kk,
JSON_VALUE(value,'$.value1') as value1,
JSON_QUERY(value,'$.value2') as value2_query,
JSON_VALUE(value,'$.value3') as value3,
t.vv21,t.vv22
from openJson(@json_str) as a
cross apply 
(
select JSON_VALUE(value,'$.vv21') as vv21,
			 JSON_VALUE(value,'$.vv22')as vv22 
from openJson(Json_Query(value,'$.value2')) as b
) as t