4

Here is my code:

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

When I try to remove the reportType field in 2nd line of code my output should be the one by removing reportType from searchQuery like below

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

Instead of that it is like below

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

I can not understand this behavior of SQL Server. Is there any help for this?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Maulik Modi
  • 1,205
  • 12
  • 22
  • 1
    This sure looks like a bug. Smallest repro: `SELECT JSON_MODIFY('{"a":{"b":""},"c":{"d":"","e":""}}', '$.a.b', NULL)` is `{"a":{"e":""}}`, while `SELECT JSON_MODIFY('{"a":{"b":""},"c":{"d":""}}', '$.a.b', NULL)` (note: only one property on the second object) gives the correct `{"a":{},"c":{"d":""}}`. If `strict` is used in the path, both cases correctly set `a.b` to `null`, so it seems like only deleting misfires. – Jeroen Mostert Jul 26 '18 at 12:43
  • 1
    Furthermore, this fails only when attempting to delete the only property of an object: `SELECT JSON_MODIFY('{"a":{"b":"","c":""},"d":{"e":"","f":""}}', '$.a.b', NULL)` yields the correct `{"a":{"c":""},"d":{"e":"","f":""}}`, and `SELECT JSON_MODIFY('{"a":{"b":"","c":""},"d":{"e":"","f":""}}', '$.a.c', NULL)` yields `{"a":{"b":""},"d":{"e":"","f":""}}`. This suggests some (fairly complicated) workarounds by adding dummy members and testing for emptiness. – Jeroen Mostert Jul 26 '18 at 12:52
  • Certainly looks like a bug. Nice find guys. I added a fiddle with @JeroenMostert example. http://sqlfiddle.com/#!18/dc377/6 – Jacob H Jul 26 '18 at 19:14
  • Repro here on SQL Server 2019 https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=36c58d18111230cf4f09b8ae8b3bcb73 – Charlieface Jul 17 '22 at 04:42

1 Answers1

3

Here is a workaround: extract, edit, and replace the child object as an object.

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
  • 1,490
  • 2
  • 11
  • 12