JSON_MODIFY is not working properly in SQL Server 2017

Question

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?

Answer 1

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