2

I am trying to remove nested property from a json using json_modify function and i am getting unexpected result.

declare @json as varchar(200) = '{"Location":{"Type":"town"},"Nearby":{"Radius":100000,"Latitude":38,"Longitude":10}}'
select json_modify(@json, '$.Location.Type', null)

I would expect to get:

{"Nearby":{"Radius":100000,"Latitude":38,"Longitude":10}}

but instead i get:

{"Location":{"Latitude":38,"Longitude":10}}

which does look completely wrong. Is there an explanation of such a behavior?

Improve this question
8
  • The 2nd parameter for JSON_MODIFY should be just $.Location, as you want to remove the entire Location entity. Commented May 25, 2022 at 9:48
  • @Larnu this 'works' for that case but i am more interested in a 'weirdness' of the behavior. Commented May 25, 2022 at 9:51
  • Honsetly, I'm not sure why you would expect {"Nearby":{"Radius":100000,"Latitude":38,"Longitude":10}},. I would more likely expect {"Location":"","Nearby":{"Radius":100000,"Latitude":38,"Longitude":10}} if you're just removing the Type entity. Commented May 25, 2022 at 9:53
  • 2
    This indeed does not comport with the documented behavior, as far as I can tell. If you prepend the path with strict, the Type property is set to null (as expected) and Nearby is retained. It looks like an edge case (or, dare I say it, bug) where the (indirect) removal of the parent object ends up shuffling the remaining properties around entirely, "as if" the second object had been deleted too. Commented May 25, 2022 at 10:00
  • 3
    Yeah, this is a bug. A minimal pair to demonstrate: select json_modify('{"a":{"x":1}, "b":{"y":1}}', '$.a.x', null) works correctly, select json_modify('{"a":{"x":1}, "b":{"y":1,"z":2}}', '$.a.x', null) does not. I'd love to have a look at the code that manages to muck this up, it must be an interesting mistake. :P Commented May 25, 2022 at 10:10
Related questions
5
JSON_MODIFY Remove Array Objects
3
T-SQL Json_modify to append a property to each object
0
SQL Function call returning unexpected result
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.