Sample Header Ad - 728x90

json_modify on condition

1 vote
1 answer
3183 views
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