Skip to content
Advertisement

SQL server: How to Modify a JSON element value in a nested JSON array

If i have below JSON (@CarDataJsontest) e.g

{
  "house": {
    "gate": [
      "Car1",
      "Car911",
      "Car3",
      "Car4"
    ]
  }
}

If i need to do is to modify the car911 to car2 all i have to do is below

SET @CarDataJsontest= JSON_MODIFY(@CarDataJsontest, '$.house.gate[1]','Car2')

Now I have a JSON @CarDataJson which is something like below

{
  "house": {
    "gate": [
      [
        "Car1",
        "Car911",
        "Car3",
        "Car4"
      ]
    ]
  }
}

What do I need to do now to modify the car911 to car2?

Because using below Query

SET @CarDataJson = JSON_MODIFY(@CarDataJson , '$.house.gate[0].[1]','Car2')

i just get an error.

Unexpected character ‘[‘ is found at position X

Advertisement

Answer

Thanks to @JeroenMostert ‘s comment

If I have a JSON @CarDataJson which is something like below

{
  "house": {
    "gate": [
      [
        "Car1",
        "Car911",
        "Car3",
        "Car4"
      ]
    ]
  }
}

All I need to do to modify the car911 to car2 is

SET @CarDataJson = JSON_MODIFY(@CarDataJson , '$.house.gate[0][1]','Car2')
7 People found this is helpful
Advertisement