SQL Server JSON_MODIFY is adding extra "\" in front of "/"

2guxujil  于 2024-01-05  发布在  其他
关注(0)|答案(1)|浏览(122)

I am trying to insert a value in my existing JSON by using inbuilt JSON functions in SQL Server.

Existing JSON:

  1. {
  2. "array": [
  3. {
  4. "type": "_api",
  5. "content": {
  6. "acId": "sometext/567890"
  7. }
  8. }
  9. ]
  10. }
  11. SET @JSONData = JSON_MODIFY(@JSONData,'$.array[0].content.uId ', 'sometext/1234/locations/1234')

But the resulted value inserted in JSON is coming as shown below.

I need it to come as sometext/1234/locations/1234 but it got added as sometext\/1234\/locations\/1234 .

  1. {
  2. "array": [
  3. {
  4. "type": "_api",
  5. "content": {
  6. "acId": "sometext/567890",
  7. "uId": "sometext\/1234\/locations\/1234"
  8. }
  9. }
  10. ]
  11. }
qybjjes1

qybjjes11#

Encapsulate your JSON_MODIFY with replace function to the incorrect characters:

  1. SET @JSONData = REPLACE(JSON_MODIFY(@JSONData,'$.array[0].content.uId', 'sometext/1234/locations/1234'), '\/', '/')

相关问题