SQL Server 操作JSON数据库列

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 @JSONSELECT * 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]--TestDECLARE @JSONText NVARCHAR(MAX);SET @JSONText = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SELECT @JSONTextSET @JSONText = JSON_MODIFY(@json, '$.info.address[1].town', 'London');
SET @JSONText = JSON_MODIFY(@json, 'append $.info.address', N'{"town":"BeiJing"}');SELECT @JSONText