sqlserver：存储Json数据
环境：
window 10 x64 专业版
Microsoft SQL Server 2019 (RTM) Enterprise Edition (64-bit)
参照：
官⽅⽂档：
官⽅⽂档：
官⽅⽂档：
官⽅⽂档：
⼀、SqlServer是如何⽀持Json的？
Sql Server 从 2016 开始⽀持了⼀些 json 操作，但在SqlServer中Json还是被存储为字符串，只不过是
符合JSON格式的字符串会允许⼀些JSON特有的操作，如：json对象的操作函数、json对象与关系表格的相互转换等，下⾯先介绍json对象的操作函数，然后再来说⼀下json对象和关系表格的相互转换。
⼆、Json操作相关函数
2.1 判断字符串是否符合JSON格式规范ISJSON()
ISJSON函数是按照JSON格式规范进⾏判断的，JSON的格式规范参照。
select ISJSON('{}')as'{}',
ISJSON('')as'空',
ISJSON('{"name":"xiaoming","age":20}')as'{...}',
ISJSON('[]')as'[]',
ISJSON('[{"name":"⼩明"}]')as'[{...}]'
2.2 从Json对象中提取标量值JSON_VALUE
注意：这个函数是从JSON对象中提取标量值(字符串、数字或true/false)。
json中包含六类数据：0：null、1：字符串、2：数字、3：true/false、4：数组、5：对象
如果你试图⽤JSON_VALUE从json字符串中取出对象或数组，你将得到⼀个NULL（默认为null，严格模式下将报错）。
关于路径表达式：
在JSON_VALUE/OPENJSON/JSON_QUERY/JSON_MODIFY这四个函数中你将会使⽤到路径表达式。
路径表达式有两种模式宽松(lax)和严格(strict)并且默认是宽松模式。
在宽松模式下，当你取值出错时（⽐如你⽤JSON_VALUE取⼀个对象或者是取⼀个不存在的属性），返回的是NULL。强制使⽤宽松模式的实例：select JSON_VALUE('{}','lax $.name')
在严格模式下，当你取值出错时，它就直接报错。强制使⽤严格模式的实例：select JSON_VALUE('{}','strict $.name')
路径表达式语法：
使⽤$代替json对象本⾝；
取对象的属性使⽤$.prop格式，如果属性名包含特殊格式，则使⽤"包裹，如：$."first name"；
取数组的语法⽰例'$[0].name'、'$.arr[0].name'；
declare@jsontext nvarchar(max);
set@jsontext='
{
"name": "⼩明",
"first name": "first xiaoming",
"age": 20,
"sex": null,
"info": {
"addr": "xiaominglu"
},
"books": [{
"name": "语⽂",
"score": 85.5
}, {
"name": "数学",
"score": 98
}]
}
';
select JSON_VALUE(@jsontext,'$.name')as'$.name',
JSON_VALUE(@jsontext,'$.abc')as'$.abc',
JSON_VALUE(@jsontext,'$.age')as'$.age',
JSON_VALUE(@jsontext,'$.sex')as'$.sex',
JSON_VALUE(@jsontext,'$.info')as'$.info',
JSON_VALUE(@jsontext,'$.info.addr')as'$.info.addr',
JSON_VALUE(@jsontext,'$.books')as'$.books',
JSON_VALUE(@jsontext,'$.books[0].name')as'$.books[0].name',
JSON_VALUE(@jsontext,'$.books[1].score')as'$.books[1].score',
JSON_VALUE(@jsontext,'$.books[2].name')as'$.books[2].name'
json转换对象
2.3 从Json字符串中提取对象或数组JSON_QUERY
这个函数和JSON_VALUE是类似的，但它返回的是⼀个json对象，⽽不是标量值，如果你试图⽤JSON_QUERY函数返回⼀个标量值，那么你将得到⼀个NULL。
注意：因为这个函数返回的是⼀个json对象，所以可⽤在JSON_MODIFY的赋值中，以防⽌SqlServer⾃动转义成字符串。
declare@jsontext nvarchar(max);
set@jsontext='
{
"name": "⼩明",
"first name": "first xiaoming",
"age": 20,
"sex": null,
"info": {
"addr": "xiaominglu"
},
"books": [{
"name": "语⽂",
"score": 85.5
}, {
"name": "数学",
"score": 98
}]
}
';
select
JSON_QUERY(@jsontext)as'⽆path',
JSON_QUERY(@jsontext,'$')as'$',
JSON_QUERY(@jsontext,'$.name')as'$.name',
JSON_QUERY(@jsontext,'$.info')as'$.info',
JSON_QUERY(@jsontext,'$.abc')as'$.abc',
JSON_QUERY(@jsontext,'$.books')as'$.books',
JSON_QUERY(@jsontext,'$.books[0]')as'$.books[0]'
2.4 更改JSON字符串的内容JSON_MODIFY
注意：这⾥只介绍宽松模式。
借助JSON_MODIFY函数，我们可以实现对JSON对象的属性新增、删除、更新操作，以及扩展的改属性名等操作。如果我们操作的属性是⼀个⾮标量的话，我们还需要借助JSON_QUERY函数。
2.4.1 使⽤JSON_MODIFY更新JSON对象属性值
-- 更改json对象name属性值
DECLARE@info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT@info
SET@info=JSON_MODIFY(@info,'$.name','Mike')
PRINT@info
2.4.2 使⽤JSON_MODIFY给JSON对象添加属性
--给json对象添加surname属性
DECLARE@info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT@info
SET@info=JSON_MODIFY(@info,'$.surname','Smith')
PRINT@info
2.4.3 使⽤JSON_MODIFY删除JSON对象的属性
--删除json对象的name属性
DECLARE@info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT@info
SET@info=JSON_MODIFY(@info,'$.name',NULL)
PRINT@info
2.4.4 当json对象的属性为⾮标量值（对象/数组）时的增删改操作
如果我们要更改的属性是⼀个数组或对象的话，我们需要使⽤JSON_QUERY函数以防⽌系统将属性值更改为转义后的字符串。下⾯是⼀个错误的⽰例：
-- 在John的技能表⾥添加"VB"技能并将它排在第⼀位
DECLARE@info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT@info
SET@info=JSON_MODIFY(@info,'$.skills','["VB","C#","SQL"]')
PRINT@info
注意，上⾯输出的属性skills是⼀个字符串，⽽不是⼀个数组。
正确的书写⽅法如下：
DECLARE@info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT@info
SET@info=JSON_MODIFY(@info,'$.skills',JSON_QUERY('["VB","C#","SQL"]'))
PRINT@info
2.4.5 当json对象的属性为数组时的快速添加操作
如果我们想向数组中添加⼀个值的话，有⼀个快速的命令可以让我们使⽤，如下：
DECLARE@info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT@info
SET@info=JSON_MODIFY(@info,'append $.skills','Azure')
PRINT@info
2.4.6 嵌套使⽤的JSON_MODIFY
可以嵌套使⽤JSON_MODIFY，看下⾯的⽰例：
DECLARE@info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT@info
SET@info=JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@info,'$.name','Mike'),'$.surname','Smith'),'append $.skills','Azure')
PRINT@info
2.4.6.1 修改json对象的属性名称
我们可以嵌套使⽤JSON_MODIFY函数，以实现修改json对象属性名称的⽬的，如下：
DECLARE@product NVARCHAR(100)='{"price":49.99}'
PRINT@product
SET@product=
JSON_MODIFY(
JSON_MODIFY(@product,'$.Price',CAST(JSON_VALUE(@product,'$.price')AS NUMERIC(4,2))),
'$.price',
NULL
)
PRINT@product
2.4.6.2 使json对象的属性值递增
我们可以嵌套使⽤JSON_MODIFY函数，以实现将给定json对象的属性值递增的⽬的，如下：
DECLARE@stats NVARCHAR(100)='{"click_count": 173}'
PRINT@stats
SET@stats=JSON_MODIFY(@stats,'$.click_count',
CAST(JSON_VALUE(@stats,'$.click_count')AS INT)+1)
PRINT@stats
三、使⽤OPENJSON将json对象转换为关系表
openjson的完整语法为：
OPENJSON( jsonExpression [, path ])[<with_clause>]
<with_clause> ::=WITH( { colName type[ column_path ][AS JSON ] } [,...n ])
下⾯，我们逐步探索OPENJSON的解析过程
3.1 直接使⽤OpenJson(’{}’)
默认情况下OPENJSON会将jsonExpression表⽰的json对象解析为table(key,value,type)的表格形式，如下：DECLARE@json NVarChar(2048)= N'{
"Null_value": null,
"String_value": "John",
"DoublePrecisionFloatingPoint_value": 45,
"DoublePrecisionFloatingPoint_value": 2.3456,
"BooleanTrue_value": true,
"BooleanFalse_value": false,
"Array_value": ["a","r","r","a","y"],
"Object_value": {"obj":"ect"}
}';
SELECT*FROM OpenJson(@json);
对于数组，openjson也默认将它转为这样的三列表格，如下：
DECLARE@json NVarChar(2048)= N'[
null,
"Jack",
"Jone",
"Tom",
15,
false,
[1,2,3],
{"age":18,"name":"xiaoming"}
]
';
SELECT*FROM OpenJson(@json);
如果json对象为空对象或空数组的话，那么转换成的表格将没有数据，如下：
select*from OPENJSON('{}')
3.2 使⽤path参数将json对象中的指定部分进⾏转换
上⾯默认情况下，OPENJSON将json对象整体进⾏转换，我们可以添加path参数来控制仅转换json对象的⼀部分。⽰例如下：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。