title: Update JSON value in array of objects
body: <p>Recently, I came across a problem where I needed to update/modify JSON data stored in database column 'Column1'</p>
  <p>Sample Data Column1:</p>
  <pre><code>{'fields':[{'id':1,'name':'product 1'},{'id':2,'name':'product 2'},{'id':3,'name':'product 3'}]}
  </code></pre>
  <p>Working on below query but could not make it work.</p>
  <pre><code>UPDATE c
  SET Column1 = JSON_MODIFY(Column1, '$.name', '{new name}')
  FROM TestTable c
  CROSS APPLY OPENJSON(c.Column1, '$.fields' )  p
  WHERE JSON_VALUE(p.value, '$.name') = 'name'
  </code></pre>
  <p>If someone can point me to right direction with any links, I will be thankful. Current query is operating on first item, I just need it to be able to work for all array items.
  Thanks for you time.
  Lot of people talking about not using the query to update JSON at all.</p>
  <p>With this query I am getting:</p>
  <blockquote>
  <p>JSON text is not properly formatted. Unexpected character 'b' is found at position 0.</p>
  </blockquote>
  <p>But when I replace <code>&quot;$.fields[0]&quot;</code> with <code>'$.fields'</code>, it updates 0 rows</p>