title: In Biqquery, how to filter an array of Struct on matching multiple fields in the Struct using Standard Sql?
body: <p>Here is the record layout of the table  (load_history) I am trying to use filter on using Standard Sql (because legacy sql will probably become obsolete at some point):</p>
  
  <pre><code>[
  {
      "mode": "NULLABLE",
      "name": "Job",
      "type": "RECORD",
      "fields": [
          {
            "mode": "NULLABLE",
            "name": "name",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "start_time",
            "type": "TIMESTAMP"
          },
          {
            "mode": "NULLABLE",
            "name": "end_time",
            "type": "TIMESTAMP"
          },
          {
      ]
  },      
  {
      "mode": "REPEATED",
      "name": "source",
      "type": "RECORD",
      "description": "source tables touched by this job",
      "fields": [     
          {
            "mode": "NULLABLE",
            "name": "database",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "schema",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "table",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "partition_time",
            "type": "TIMESTAMP"
          }    
      ]
  }
  ]      
  </code></pre>
  
  <p>I need to filter and select only records where there is an entry in array "source" whose  "schema" &amp; "table" fields match certain values (ex. schema='log' AND table='customer' in the same array entry). </p>
  
  <p>The following works when filtering only on one field in the Struct (schema name) :</p>
  
  <pre><code>select name, array(select x from unnest(schema) as x where x ='log' ), table
  from (select job.name , array(select schema from unnest(source)) as schema, 
        array(select table from unnest(source)) as table
        from  config.load_history)
  </code></pre>
  
  <p>However, I can't make it work to filter on combination of fields in the same array entry.</p>
  
  <p>Would appreciate your help</p>