title: Using Flatten to select where var1 (non-repeated) = "abc" from a bigquery table which contains multiple nested variables?
body: <p>I'm fairly new to BigQuery (3rd day of using it with no training), I'm just trying to get my head around nested fields etc.</p>
  
  <p>I've looked at the following resources and used the personsdata example on the google bigquery docs link</p>
  
  <p><a href="https://cloud.google.com/bigquery/docs/data" rel="nofollow noreferrer">https://cloud.google.com/bigquery/docs/data</a></p>
  
  <p><a href="https://chartio.com/resources/tutorials/how-to-flatten-data-using-google-bigquerys-legacy-vs-standard-sql/" rel="nofollow noreferrer">https://chartio.com/resources/tutorials/how-to-flatten-data-using-google-bigquerys-legacy-vs-standard-sql/</a></p>
  
  <p>I'd like to run the below query:</p>
  
  <pre><code>select *
  from [dataset.tableid]
  where fullname = 'John Doe'
  </code></pre>
  
  <p>If I run this, I get the following error:</p>
  
  <p><em>Error: Cannot output multiple independently repeated fields at the same time. Found children_age and citiesLived_place</em></p>
  
  <p>From reading the above articles this isn't possible because you need to flatten the results, which from what I can understand just duplicates all the none repeated variables i.e.</p>
  
  <p>Fullname |  age |  gender |  Children.name |  children.age</p>
  
  <p><strong>John Doe |  22  |  Male</strong>   |  John          |  5</p>
  
  <p><strong>John Doe |  22  |  Male</strong>   |  Jane          |  7</p>
  
  <p>One of the above articles suggests that you can still use the where statements by using the flatten function in bigquery:</p>
  
  <pre><code>select fullname,
  age,
  gender,
  citiesLived.place
  FROM (FLATTEN([dataset.tableId], children))
  WHERE
  (citiesLived.yearLived &gt; 1995) AND
  (children.age &gt; 3)
  GROUP BY fullName, age, gender, citiesLived.place
  </code></pre>
  
  <p>If I change this to:</p>
  
  <pre><code>select *
  FROM (FLATTEN([dataset.tableId], children))
  WHERE fullname = 'John Doe'
  </code></pre>
  
  <p>Then this works fine and gives me what I need however if I change to this:</p>
  
  <pre><code>select *
  FROM (FLATTEN([dataset.tableId], citieslived))
  WHERE fullname = 'John Doe'
  </code></pre>
  
  <p>Then I get the following error: </p>
  
  <p><em>Error: Cannot output multiple independently repeated fields at the same time. Found children_age and citiesLived_yearsLived</em></p>
  
  <p>Can someone explain why this will work flattening based on "Children" but not "CitiesLived" and how to know what variables to use within flatten with more complex datasets with multiple nested variables?</p>
  
  <p>Thank you in advance</p>