Forum Discussion
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:
["purchaseorders":
{
"orders":[{
"ordernum": "A1",
"orderstatus": null
},
{
"ordernum": "A2",
"orderstatus": { "Packing":{"status":"completed"}, "Shipping":{"status":"Completed"}
} } ] }]
Written below query to process. But after update, it inserts backslash and double quotes that encloses the whole string
{ \"ordernum\": \"A1\", \"orderstatus\": "{ \"Packing\":{\"status\":\"completed\"}, \"Shipping\":{\"status\":\"Completed\"}\"}
Query used:
update
purchaseorder
set
details = JSON_MODIFY(details, CONCAT('$.purchaseorders.orders[',@i,'].orderstatus), statusText);
any help to get this updated properly for the orderstatus is null. thanks in advance
1 Reply
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 JSON_MODIFY (Transact-SQL)
