title: Flattening a nested and repeated structure in BigQuery (standard SQL)
body: <p>There are a lot of posts on unnesting repeated fields in BigQuery -- but, being new to this environment, I have tried almost every code variation I found to flatten a data file.  But, I cannot seem to produce one without creating blanks in the <code>id</code> field.  It seem like I need to unflatten a nested variable?</p>
  <p>I'm using a COVID Dimensions data set that is part of the public collection.  Here is some minimal code that produces my problem:</p>
  <pre><code>SELECT
     id,
     authors
  FROM
     `covid-19-dimensions-ai.data.publications`
  CROSS JOIN 
     UNNEST(authors)
  LIMIT 1000 
  </code></pre>
  <p>And, here is the JSON structure after running this query. Everything is flattened with the structure I want, but I don't know how to fill in / avoid the blank id variables.</p>
  <pre><code>{
      &quot;id&quot;: &quot;pub.1130234899&quot;,
      &quot;authors&quot;: {
        &quot;first_name&quot;: &quot;Eric M&quot;,
        &quot;last_name&quot;: &quot;Yoshida&quot;,
        &quot;initials&quot;: null,
        &quot;researcher_id&quot;: &quot;ur.01071531321.03&quot;,
        &quot;grid_ids&quot;: [
          &quot;grid.17091.3e&quot;
        ],
        &quot;corresponding&quot;: false,
        &quot;raw_affiliations&quot;: [
          &quot;Division of Gastroenterology, University of British Columbia, Vancouver, British Columbia, Canada&quot;
        ],
        &quot;affiliations_address&quot;: [
          {
            &quot;grid_id&quot;: &quot;grid.17091.3e&quot;,
            &quot;city_id&quot;: &quot;6173331&quot;,
            &quot;state_code&quot;: &quot;CA-BC&quot;,
            &quot;country_code&quot;: &quot;CA&quot;,
            &quot;raw_affiliation&quot;: &quot;Division of Gastroenterology, University of British Columbia, Vancouver, British Columbia, Canada&quot;
          }
        ]
      }
    }
  </code></pre>