Forum Discussion

sooriyathoppan's avatar
sooriyathoppan
Copper Contributor
Oct 29, 2024

SQL server query - Update the json attribute in a column query updating double quotes and back slash

Hi, i have written an update query to update a specific attribute(orderstatus) with value ({ "Packing":{"status":"completed}, "Shipping":{"status":"Completed"}) if its null   Example: ["purchaseor...
rodgerkong's avatar
rodgerkong
Iron Contributor
Oct 30, 2024

sooriyathoppan 

Use JSON_QUERY() function before the string will be used by JSON_MODIFY(), this will avoid JSON_MODIFY escape the string. like this

update
  purchaseorder
set 
  details = JSON_MODIFY(details, CONCAT('$.purchaseorders.orders[',@i,'].orderstatus), JSON_QUERY(statusText));

 Check the article https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?view=sql-server-ver16#e-modify-a-json-object

Resources