title: Working with Structs within Arrays for new BigQuery Standard SQL
body: <p>I'm trying to find rows with duplicate fields in an array of structs within a Google BigQuery table, using the new Standard SQL. The data in the table (simplified) where each row looks a bit like this:</p>
  
  <pre><code>{
    "Session": "abc123",
    "Information" [
      {
        "Identifier": "e8d971a4-ef33-4ea1-8627-f1213e4c67dc"
      },
      {
        "Identifier": "1c62813f-7ec4-4968-b18b-d1eb8f4d9d26"
      },
      {
        "Identifier": "e8d971a4-ef33-4ea1-8627-f1213e4c67dc"
      }
    ]
  }
  </code></pre>
  
  <p>My end goal is to display the rows that have <code>Information</code> entities with duplicate <code>Identifier</code> values present. However, most of the queries I attempt get an error message of the following form:</p>
  
  <pre><code>Cannot access field Identifier on a value with type ARRAY&lt;STRUCT&lt;Identifier STRING&gt;&gt;
  </code></pre>
  
  <p>Is there a way to work with the data inside of a <code>STRUCT</code> within an <code>ARRAY</code>?</p>
  
  <hr>
  
  <p>Here's my first attempt at a query:</p>
  
  <pre><code>SELECT
    Session,
    Information
  FROM
    `events.myevents`
  WHERE
    COUNT(DISTINCT Information.Identifier) != ARRAY_LENGTH(Information.Identifier)
  LIMIT
    1000
  </code></pre>
  
  <p>And another using a subquery:</p>
  
  <pre><code>SELECT
    Session,
    Information
  FROM (
    SELECT
      Session,
      Information,
      COUNT(DISTINCT Information.Identifier) AS info_count_distinct,
      ARRAY_LENGTH(Information) AS info_count
    FROM
      `events.myevents`
    WHERE
      COUNT(DISTINCT Information.Identifier) != ARRAY_LENGTH(Information.Identifier)
    LIMIT
      1000)
  WHERE
    info_count != info_count_distinct
  </code></pre>