Sql Server 从 2016 开始支持了一些 json 操作,但在SqlServer中Json还是被存储为字符串,如下:
use [tempdb]
declare @JSON nvarchar(max)
set @JSON=N'{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 1,
"pets": [
{ "givenName": "Goofy" },
{ "givenName": "Shadow" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 8
}
],
"address": { "state": "NY", "county": "Manhattan", "city": "NY" },
"creationDate": 1431620462,
"isRegistered": false
}'
--此 JSON 文档包含嵌套的复杂元素,存储在下面的示例表中:
--CREATE TABLE Families (
-- id int identity constraint PK_JSON_ID primary key,
-- doc nvarchar(max)
--)
--insert into Families(doc) select @JSON
SELECT * FROM Families WHERE ISJSON(doc) > 0
--使用 JSON_VALUE 函数从 JSON 文本中提取值
SELECT JSON_VALUE(f.doc, '$.id') AS Name,
JSON_VALUE(f.doc, '$.address.city') AS City,
JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'WakefieldFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC, JSON_VALUE(f.doc, '$.address.state') ASC
--WakefieldFamily NY Manhattan
--使用 JSON_QUERY 函数从 JSON 文本中提取对象或数组
SELECT JSON_QUERY(f.doc, '$.address') AS Address,
JSON_QUERY(f.doc, '$.parents') AS Parents,
JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'WakefieldFamily'
--分析嵌套式 JSON 集合
SELECT JSON_VALUE(f.doc, '$.id') AS Name,
JSON_VALUE(f.doc, '$.address.city') AS City,
c.givenName, c.grade
FROM Families f
CROSS APPLY OPENJSON(f.doc, '$.children') WITH(grade int, givenName nvarchar(100)) c
--查询嵌套式分层 JSON 子数组
SELECT familyName,
c.givenName AS childGivenName,
c.firstName AS childFirstName,
p.givenName AS petName
FROM Families f
CROSS APPLY OPENJSON(f.doc) WITH (familyName nvarchar(100), children nvarchar(max) AS JSON)
CROSS APPLY OPENJSON(children) WITH (givenName nvarchar(100), firstName nvarchar(100), pets nvarchar(max) AS JSON) as c
OUTER APPLY OPENJSON (pets) WITH (givenName nvarchar(100)) as p
--JSON_VALUE 和 JSON_QUERY 之间的主要区别在于 JSON_VALUE 返回标量值,而 JSON_QUERY 返回数组或对象。
--use [AdventureWorks]
--修改 JSON 对象
DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT @info
-- Update skills array
SET @info=JSON_MODIFY(@info,'$.skills',JSON_QUERY('["C#","T-SQL","Azure"]'))
PRINT @info
--修改 JSON 对象
DECLARE @RespData NVARCHAR(max)=N'{"code": "000","message": "成功","data": {"secretKey": "","content": "{\"rule_result\":{\"risk_level\":\"\",\"reason_code\":[],\"hitted_rules\":[]}}"}}'
declare @content NVARCHAR(max)=JSON_VALUE(@RespData,'$.data.content')
SET @content=JSON_MODIFY(@content,'$.rule_result.hitted_rules',JSON_QUERY(N'[{"name":"ZZC_CRS0027","description":"申请人最近7天到30天在网贷机构出现过","rule_type":"跨机构比对","risk_level":"M"}]'))
SET @RespData=JSON_MODIFY(@RespData,'$.data.content',@content)
--select @content,@RespData
select JSON_VALUE(@RespData,'$.code'),JSON_VALUE(@RespData,'$.message'),JSON_VALUE(@RespData,'$.data.content'),JSON_VALUE(JSON_VALUE(@RespData,'$.data.content'),'$.rule_result.hitted_rules[0].name')
--https://learn.microsoft.com/zh-cn/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver16
--drop table [Families]
--Test
DECLARE @JSONText NVARCHAR(MAX);
SET @JSONText = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SELECT @JSONText
SET @JSONText = JSON_MODIFY(@json, '$.info.address[1].town', 'London');
SET @JSONText = JSON_MODIFY(@json, 'append $.info.address', N'{"town":"BeiJing"}');
SELECT @JSONText