As in my [previous question](https://dba.stackexchange.com/questions/304001/enumerating-json-arrays-with-openjson) , I am working on migrating my XML based procedures to JSON ones. My newest struggle is with json_modify
I'm used to using .modify with XPATH to be able to modify XMLs on the fly.
For example, if I have an XML like the following (example taken from SqlShack)
Josef
Brown
jo0@adventure-works.com
M
Katie
McAskill-White
katie0@adventure-works.com
Maddie
Brown
madhatter@adventure-works.com
And I wanted to change the names of all users with user_id="2" to "Nat", I could write the following:
xml.modify('replace value of (/users/user[@user_id=("2")]/fname/text()) with "Nat"')
Now let's talk about JSON. Suppose I had the following JSON
{"Users":
[{"user_id":1,"fname":"Josef","lname":"Brown","email":"jo0@adventure-works.com","gender":"M"},
{"user_id":2,"fname":"Katie","lname":"McAskill-White","email":"katie0@adventure-works.com"},
{"user_id":2,"fname":"Maddie","lname":"Brown","email":"madhatter@adventure-works.com"}]
}
I want to do the same I did above, but with this JSON. How do I use json_modify to do this?
Asked by Rushabh Mehta
(251 rep)
Jan 3, 2022, 05:37 PM
Last activity: Jan 4, 2022, 03:25 AM
Last activity: Jan 4, 2022, 03:25 AM