这是我的代码:
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的这种行为。有没有帮助可以提供?
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 MostertSELECT 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