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?

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
– Jeroen Mostert, Commented Jul 26, 2018 at 12:43
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
– Jeroen Mostert, Commented Jul 26, 2018 at 12:52
Certainly looks like a bug. Nice find guys. I added a fiddle with @JeroenMostert example. sqlfiddle.com/#!18/dc377/6 — Jacob H
– Jacob H, Commented Jul 26, 2018 at 19:14

John Ingle · Accepted Answer · 2018-08-30 16:24:02Z

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

answered Aug 30, 2018 at 16:24

John Ingle

1,6602 gold badges12 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON_MODIFY is not working properly in SQL Server 2017

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related