title: BigQuery update an item in repeated field (array)?
body: <p>How to update specific item in repeated field in BigQuery? </p>
  
  <p>For example I just want to update the comments that is match comment's <code>id = 1</code>, what is BigQuery SQL syntax for it?</p>
  
  <pre><code>{
    "id": 1,
    "name": "Data A",
    "comments": [
      {
        "timestamp": 1586333435,
        "id": 1,
        "comment": "Hello World"
      },
      {
        "timestamp": 1586333635,
        "id": 2,
        "comment": "Hello World 2"
      }
    ]
  }
  </code></pre>
  
  <p>My update statement using as follow, but it replace the whole array.</p>
  
  <pre><code>UPDATE `projectA.dataSetA.Post`
      SET comments = ARRAY(
          SELECT AS STRUCT * REPLACE('Hello World Now' AS comment) FROM UNNEST(comments) AS c WHERE c.id=1)
      WHERE id='1';
  </code></pre>