title: In Athena how do I query a member of a struct in an array in a struct?
body: <p>I am trying to figure out how to query where I am checking the value of <code>usage</code> given the following table creation:</p>
  
  <pre><code>CREATE EXTERNAL TABLE IF NOT EXISTS foo.test (
       `id` string,
       `foo` struct&lt; usages:array&lt; struct&lt; usage:string,
       method_id:int,
       start_at:string,
       end_at:string,
       location:array&lt;string&gt; &gt;&gt;&gt; 
  ) PARTITIONED BY (
           timestamp date 
  ) 
  ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
  WITH SERDEPROPERTIES (
           'serialization.format' = '1' ) LOCATION 's3://foo.bar/' TBLPROPERTIES ('has_encrypted_data'='false');
  </code></pre>
  
  <p>I would like to have a query like:</p>
  
  <pre><code>SELECT * FROM "foo"."test" WHERE foo.usages.usage is null;
  </code></pre>
  
  <p>When I do that I get: </p>
  
  <blockquote>
    <p>SYNTAX_ERROR: line 1:53: Expression "foo"."usages" is not of type ROW</p>
  </blockquote>
  
  <p>If I make my query where I directly index the array as seen in the following it works.</p>
  
  <pre><code>SELECT * FROM "foo"."test" WHERE foo.usages[1].usage is null;
  </code></pre>
  
  <p>My overall goal though is to query across all items in the <code>usages</code> array and find any row where at least one item in the <code>usages</code> array has a member <code>usage</code> that is null.</p>