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 @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