SQL Server解析Json格式数据

逃离我推掉我的手 2023-07-08 02:46 10阅读 0赞

主要介绍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

![image.png][]

# 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

![image.png][image.png 1]

注：

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  
![image.png][image.png 2]

# 进阶用法:多层复杂的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

![image.png][image.png 3]

[image.png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAyMC9wbmcvNjcxOTcwLzE1ODI1MzIxMzgxNTYtMDljMmVkOGYtNTY3Mi00ZmE0LThmMWYtMDE4NWZlYjA0YzVjLnBuZw?x-oss-process=image/format,png#align=left&display=inline&height=252&name=image.png&originHeight=504&originWidth=1556&size=218876&status=done&style=none&width=778
[image.png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAyMC9wbmcvNjcxOTcwLzE1ODI1MzMxODY1NjktODIwOWU5ZjMtOTJjMy00M2Q3LTgxYjgtNTFmZGNlM2M2MzFlLnBuZw?x-oss-process=image/format,png#align=left&display=inline&height=372&name=image.png&originHeight=744&originWidth=1634&size=396620&status=done&style=none&width=817
[image.png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAyMC9wbmcvNjcxOTcwLzE1ODI1NDE2NjA3OTYtOTc1NmUyNTMtYTA1Ni00MTkyLTk1MWItNThkNGRkZjFkYjMzLnBuZw?x-oss-process=image/format,png#align=left&display=inline&height=316&name=image.png&originHeight=632&originWidth=1560&size=313904&status=done&style=none&width=780
[image.png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAyMC9wbmcvNjcxOTcwLzE1ODI1NDE4NDkyMzItNDNmZDljMjUtNmE3Yy00ZTRkLTkxN2EtYTU4ZTY4NWI5MmE5LnBuZw?x-oss-process=image/format,png#align=left&display=inline&height=473&name=image.png&originHeight=946&originWidth=1460&size=469697&status=done&style=none&width=730