Issue with SQL Server JSON_MODIFY when removing an object

Question

I have the following query:

DECLARE @OriginalJSON NVARCHAR(4000), @newjson NVARCHAR(4000);
SET @OriginalJSON = '{"Test":{"item1": {"Value" : "test1"}}, "Test2": {"item11": {"Value" : "test11"}}}';
SELECT @OriginalJSON AS 'Before Update',
JSON_MODIFY(@OriginalJSON, '$.Test.item1', NULL) AS 'Updated JSON';

which works as expected. The output is to remove the Test.item1 object, so the resulting JSON looks like this:

{"Test":{}, "Test2": {"item11": {"Value" : "test11"}}}

If I change the input JSON slightly so that Test2 contains a second object, an again try to remove Test.item1 then the returned JSON is not correct:

DECLARE @OriginalJSON NVARCHAR(4000), @newjson NVARCHAR(4000);
SET @OriginalJSON = '{"Test":{"item1": {"Value" : "test1"}}, "Test2": {"item11": {"Value" : "test11"}, "item12": {"Value" : "test12"}}}';
SELECT @OriginalJSON AS 'Before Update',
JSON_MODIFY(@OriginalJSON, '$.Test.item1', NULL) AS 'Updated JSON';

I get this:

{"Test":{ "item12": {"Value" : "test12"}}}

So Test2 has completely disappeared. item11 is gone, and item12 is now a child of Test rather than Test2.

This is happening on SQL Server 15.0.4073.23

Is there something wrong with my approach?

Answer 1

Hi，@Andrew Hall
Welcome to Microsoft T-SQL Q&A Forum!
When you add Test2 with a second object,don't forget to add [ ]

Please check this:

DECLARE @OriginalJSON NVARCHAR(4000), @newjson NVARCHAR(4000);  
SET @OriginalJSON = '{"Test":{"item1": {"Value" : "test1"}}, "Test2": [{"item11": {"Value" : "test11"}, "item12": {"Value" : "test12"}}]}';  
SELECT @OriginalJSON AS 'Before Update',  
JSON_MODIFY(@OriginalJSON, '$.Test.item1', NULL) AS 'Updated JSON';  
print @OriginalJSON  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you just want an object with properties item11 and item22,why not try JSON_QUERY like below:

DECLARE @OriginalJSON NVARCHAR(4000), @newjson NVARCHAR(4000);  
SET @OriginalJSON = '{"Test":{"item1": {"Value" : "test1"}}, "Test2": {"item11": {"Value" : "test11"}, "item12": {"Value" : "test12"}}}';  
SELECT @OriginalJSON AS 'Before Update',  
JSON_QUERY(@OriginalJSON, '$.Test2') AS 'Updated JSON';  
print @OriginalJSON  

Output: