title: How to update a specific item of an array in json object
body: <p>I'm manipulating a JSON column in a SQL Azure table/database, the JSON object is formed like this:</p>
  
  <pre><code>{
    "statusId": "5A5BC717-F33A-42A5-8E48-99531C30EC87",
    "creationDateTime": "",
    "assignations": [
      {
        "userId": "CA3B0589-B558-4FCC-93A6-560754D324FC",
        "dateTime": "",
        "isCurrentAssigned": false
      },
      {
        "userId": "CA3B0589-B558-4FCC-93A6-560754D325E8",
        "dateTime": "",
        "isCurrentAssigned": false
      },
      {
        "userId": "CA3B0589-B558-4FCC-93A6-560754D347N",
        "dateTime": "",
        "isCurrentAssigned": true
      }
    ]
  }
  </code></pre>
  
  <p>What I want to accomplish is to find a specific element inside the array "assignations" and then update some of its properties, just something like:</p>
  
  <pre><code>UPDATE MyTable
  SET JsonData = JSON_MODIFY(JsonData, '$.assignations.isCurrentAssigned', CONVERT(BIT, 0))
  FROM MyDb
  WHERE JSON_VALUE(JsonData, '$.assignations.isCurrentAssigned') = CONVERT(BIT, 1) AND
  JSON_VALUE(JsonData, '$.assignations.userId') =  CONVERT(UNIQUEIDENTIFIER, 'CA3B0589-B558-4FCC-93A6-560754D347N')
  </code></pre>
  
  <p>Of course this T-SQL is not working, I will appreciate any help on this</p>