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?

ITmeze
  • 1,902
  • 2
  • 21
  • 32
  • The 2nd parameter for `JSON_MODIFY` should be just `$.Location`, as you want to remove the entire `Location` entity. – Thom A May 25 '22 at 09:48
  • @Larnu this 'works' for that case but i am more interested in a 'weirdness' of the behavior. – ITmeze May 25 '22 at 09: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. – Thom A May 25 '22 at 09:53
  • @Larnu empty string? no... i would rather be okay with an empty property, e.g ```{"Location":{},"Nearby":{"Radius":100000,"Latitude":38,"Longitude":10}}```. But what i am getting is absolutely weird – ITmeze May 25 '22 at 09:55
  • 2
    This indeed does not comport with the [documented behavior](https://learn.microsoft.com/sql/t-sql/functions/json-modify-transact-sql), 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. – Jeroen Mostert May 25 '22 at 10:00
  • My point was more that by removing `$.Location.Type` I'm not sure why you expected `$.Location` to be removed, @ITmeze (I admit an empty string was a poor choice to demonstrate that). – Thom A May 25 '22 at 10:07
  • 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 – Jeroen Mostert May 25 '22 at 10:10
  • I thought this [looked familiar](https://stackoverflow.com/q/51538763/4137916). At least I'm not going completely senile and vaguely remembered seeing it before, but still, I should have already known. Too bad nobody's reported it to MS yet, apparently, or if they did MS is taking their sweet time! – Jeroen Mostert May 25 '22 at 10:28

0 Answers0