JSON_MODIFY在SQL Server 2017中无法正常工作

Question

这是我的代码：

DECLARE @info NVARCHAR(MAX) = '{"searchQuery":{"reportType":"ReportedHcEcg"},"pageQuery":{"pageNumber":1,"pageSize":10,"sortColumnName":"Urgent, UploaDateTime","sortOrder":"Desc"}}'
SET @info = JSON_MODIFY(@info, '$.searchQuery.reportType', NULL)
SELECT @info

当我尝试在代码的第二行删除reportType字段时，我的输出应该是从searchQuery中删除reportType后的输出，如下所示。

{"searchQuery":{},"pageQuery":{"pageNumber":1,"pageSize":10,"sortColumnName":"Urgent, UploaDateTime","sortOrder":"Desc"}}

相反，它是这样的：

{"searchQuery":{"pageSize":10,"sortColumnName":"Urgent, UploaDateTime","sortOrder":"Desc"}}

我无法理解SQL Server的这种行为。有没有帮助可以提供？

- Maulik Modi

---

这肯定是一个bug。最小复现代码：SELECT JSON_MODIFY('{"a":{"b":""},"c":{"d":"","e":""}}', '$.a.b', NULL) 是 {"a":{"e":""}}，而 SELECT JSON_MODIFY('{"a":{"b":""},"c":{"d":""}}', '$.a.b', NULL)（注意：第二个对象只有一个属性）给出了正确的 {"a":{},"c":{"d":""}}。如果在路径中使用 strict，两种情况都会正确地将 a.b 设置为 null，因此似乎只有删除操作失效。 - Jeroen Mostert

此外，仅在尝试删除对象的唯一属性时才会失败：SELECT JSON_MODIFY('{"a":{"b":"","c":""},"d":{"e":"","f":""}}', '$.a.b', NULL) 产生正确的结果 {"a":{"c":""},"d":{"e":"","f":""}}，而 SELECT JSON_MODIFY('{"a":{"b":"","c":""},"d":{"e":"","f":""}}', '$.a.c', NULL) 则产生 {"a":{"b":""},"d":{"e":"","f":""}}。这表明需要通过添加虚拟成员并测试是否为空来进行一些（相当复杂的）解决方法。 - Jeroen Mostert

肯定看起来像是一个bug。干得好，伙计们。我添加了一个@JeroenMostert示例的小提琴。http://sqlfiddle.com/#!18/dc377/6 - Jacob H

在SQL Server 2019上重现，请访问https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=36c58d18111230cf4f09b8ae8b3bcb73 - Charlieface

Answer 1

这里有一个解决方案：提取、编辑并替换子对象为一个对象。

DECLARE @info NVARCHAR(MAX) = '{"searchQuery":{"reportType":"ReportedHcEcg"},"pageQuery":{"pageNumber":1,"pageSize":10,"sortColumnName":"Urgent, UploaDateTime","sortOrder":"Desc"}}'
SET @info = JSON_MODIFY(@info, '$.searchQuery', JSON_MODIFY(JSON_QUERY(@info, '$.searchQuery'), '$.reportType', NULL))
SELECT @info

- John Ingle