title: Bigquery update / insert in nested arrays and arrays of structs
body: <blockquote>
  <p>Editing the question to have a better view ..</p>
  </blockquote>
  <p>There are 2 tables - Staging and Core.</p>
  <p>I am having trouble copying the data from Staging to Core.</p>
  <p>Conditions</p>
  <ol>
  <li>If <code>id</code>, <code>Year</code> and <code>local_id</code> matches in both staging and core -&gt; the <code>data</code> for that specific Array row should be updated from staging to core</li>
  <li>If <code>id</code> does not match in staging and core -&gt; A new Row should be inserted in CORE with values from STAGING</li>
  <li>If <code>id</code> matches but either of <code>local_id</code>/<code>Year</code> do not match, then a new row should be inserted in the <code>data</code> array.</li>
  </ol>
  <p>BigQuery schema for STAGING</p>
  <pre><code>[
      {
          &quot;name&quot;: &quot;id&quot;,
          &quot;type&quot;: &quot;STRING&quot;
      },
      {
          &quot;name&quot;: &quot;content&quot;,
          &quot;type&quot;: &quot;STRING&quot;
      },
      {
          &quot;name&quot;: &quot;createdAt&quot;,
          &quot;type&quot;: &quot;TIMESTAMP&quot;
      },
      {
          &quot;name&quot;: &quot;sourceFileName&quot;,
          &quot;type&quot;: &quot;STRING&quot;
      },
      {
          &quot;name&quot;: &quot;data&quot;,
          &quot;type&quot;: &quot;record&quot;,
          &quot;fields&quot;: [
              {
                  &quot;name&quot;: &quot;local_id&quot;,
                  &quot;type&quot;: &quot;STRING&quot;,
                  &quot;mode&quot;: &quot;NULLABLE&quot;
              },
              {
                  &quot;name&quot;: &quot;year&quot;,
                  &quot;type&quot;: &quot;INTEGER&quot;,
                  &quot;mode&quot;: &quot;NULLABLE&quot;
              },
              {
                  &quot;name&quot;: &quot;country&quot;,
                  &quot;type&quot;: &quot;STRING&quot;,
                  &quot;mode&quot;: &quot;NULLABLE&quot;
              }
             ]
      }
  ]
  </code></pre>
  <p>BigQuery schema for CORE</p>
  <pre><code>[
      {
          &quot;name&quot;: &quot;id&quot;,
          &quot;type&quot;: &quot;STRING&quot;
      },
      {
          &quot;name&quot;: &quot;content&quot;,
          &quot;type&quot;: &quot;STRING&quot;
      },
      {
          &quot;name&quot;: &quot;createdAt&quot;,
          &quot;type&quot;: &quot;TIMESTAMP&quot;
      },
      {
          &quot;name&quot;: &quot;data&quot;,
          &quot;type&quot;: &quot;record&quot;,
          &quot;mode&quot;: &quot;REPEATED&quot;,
          &quot;fields&quot;: [
              {
                  &quot;name&quot;: &quot;local_id&quot;,
                  &quot;type&quot;: &quot;STRING&quot;,
                  &quot;mode&quot;: &quot;NULLABLE&quot;
              },
              {
                  &quot;name&quot;: &quot;year&quot;,
                  &quot;type&quot;: &quot;INTEGER&quot;,
                  &quot;mode&quot;: &quot;NULLABLE&quot;
              },
              {
                  &quot;name&quot;: &quot;country&quot;,
                  &quot;type&quot;: &quot;STRING&quot;,
                  &quot;mode&quot;: &quot;NULLABLE&quot;
              }
             ]
      }
  ]
  </code></pre>
  <p>Big Query content for staging -</p>
  <pre><code>{&quot;id&quot;:&quot;1&quot;,&quot;content&quot;:&quot;content1&quot;,&quot;createdAt&quot;:&quot;2020-07-23 12:46:15.054410 UTC&quot;,&quot;sourceFileName&quot;:&quot;abc.json&quot;,&quot;data&quot;:{&quot;local_id&quot;:&quot;123&quot;,&quot;year&quot;:2018,&quot;country&quot;:&quot;PL&quot;}}
  {&quot;id&quot;:&quot;1&quot;,&quot;content&quot;:&quot;content3&quot;,&quot;createdAt&quot;:&quot;2020-07-23 12:46:15.054410 UTC&quot;,&quot;sourceFileName&quot;:&quot;abc.json&quot;,&quot;data&quot;:{&quot;local_id&quot;:&quot;123&quot;,&quot;year&quot;:2021,&quot;country&quot;:&quot;SE&quot;}}
  {&quot;id&quot;:&quot;2&quot;,&quot;content&quot;:&quot;content4&quot;,&quot;createdAt&quot;:&quot;2020-07-23 12:46:15.054410 UTC&quot;,&quot;sourceFileName&quot;:&quot;abc.json&quot;,&quot;data&quot;:{&quot;local_id&quot;:&quot;334&quot;,&quot;year&quot;:2021,&quot;country&quot;:&quot;AZ&quot;}}
  {&quot;id&quot;:&quot;2&quot;,&quot;content&quot;:&quot;content5&quot;,&quot;createdAt&quot;:&quot;2020-07-23 12:46:15.054410 UTC&quot;,&quot;sourceFileName&quot;:&quot;abc.json&quot;,&quot;data&quot;:{&quot;local_id&quot;:&quot;337&quot;,&quot;year&quot;:2021,&quot;country&quot;:&quot;NZ&quot;}}
  </code></pre>
  <p>Example Content structure <a href="https://i.stack.imgur.com/mu1uD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mu1uD.png" alt="enter image description here" /></a></p>
  <p>Big Query content for core -</p>
  <pre><code>{&quot;id&quot;:&quot;1&quot;,&quot;content&quot;:&quot;content1&quot;,&quot;createdAt&quot;:&quot;2020-07-23 12:46:15.054410 UTC&quot;,&quot;data&quot;:[{&quot;local_id&quot;:&quot;123&quot;,&quot;year&quot;:2018,&quot;country&quot;:&quot;SE&quot;},{&quot;local_id&quot;:&quot;33&quot;,&quot;year&quot;:2019,&quot;country&quot;:&quot;PL&quot;},{&quot;local_id&quot;:&quot;123&quot;,&quot;year&quot;:2020,&quot;country&quot;:&quot;SE&quot;}]}
  </code></pre>
  <p>Example Content structure <a href="https://i.stack.imgur.com/JhAGb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JhAGb.png" alt="enter image description here" /></a></p>