title: Querying optional nested JSON fields in Athena
body: <p>I have json data that looks something like:</p>
  
  <pre><code>{ "col1" : 123, "metadata" : { "opt1" : 456, "opt2" : 789 } }
  </code></pre>
  
  <p>where the various metadata fields (of which there are many) are optional and may or may not be present.</p>
  
  <p>My query is:</p>
  
  <pre><code>select col1, metadata.opt1 from "db-name".tablename
  </code></pre>
  
  <p>If <code>opt1</code> is not present in any rows, I would expect this to return all rows with a blank for the <code>opt1</code> column, but if there wasn't a row with the <code>opt1</code> in <code>metadata</code> when the crawler ran (and might still not be present in data when the query is run, as it's optional), the query fails, with:</p>
  
  <pre><code>SYNTAX_ERROR: line 2:1: Column '"metadata"."opt1"' cannot be resolved
  </code></pre>
  
  <p>I could specify these fields manually either in the schema definition (if I don't use a crawler), but then it wouldn't pick up any new metadata fields that may arrive, and specifying a static schema doesn't seem to be in the spirit of how Athena is supposed to work.</p>
  
  <p>How do I get this to function as expected (preferably without putting dummy rows in or customizing the SerDe)?</p>
  
  <p>Using SerDe <code>org.openx.data.jsonserde.JsonSerDe</code> at present.</p>
  
  <p>Thanks for any ideas.</p>