title: Athena unnest json array of string within another json array of structs
body: <p>I have the following AWS Athena create table statement: </p>
  
  <pre><code>CREATE EXTERNAL TABLE IF NOT EXISTS s2cs3dataset.s2c_storage (
           `MessageHeader` string,
           `TimeToProcess` float,
           `KeyCreated` string,
           `KeyLastTouch` string,
           `CreatedDateTime` string,
           `TableReference` array&lt;struct&lt;`BusinessObject`: string,
           `TransactionType`: string,
           `ReferenceKeyId`: float,
           `ReferencePrimaryKey`: string,
           `IncludedTables`: array&lt;string&gt;&gt;&gt;,
           `SAPStoreReference` string 
  ) 
  ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
  WITH SERDEPROPERTIES (
           'serialization.format' = '1' ) LOCATION 's3://api-dev-dpstorage-s3/S2C_INPUT/storage/' TBLPROPERTIES ('has_encrypted_data'='false');
  </code></pre>
  
  <p>From this, I want to select the following items with this query:</p>
  
  <pre><code>SELECT MessageHeader,
  TimeToProcess,
  KeyCreated,
  KeyLastTouch,
  CreatedDateTime,
  tr.BusinessObject, 
  tr.TransactionType,
  tr.ReferencePrimaryKey,
  it.IncludedTables,
  SAPStoreReference
  FROM s2c_storage
  cross join UNNEST(s2c_storage.tablereference) as p(tr)
  cross join UNNEST(tr.IncludedTables) as p(it)
  </code></pre>
  
  <p>However I get the following error:</p>
  
  <blockquote>
    <p>SYNTAX_ERROR: line 9:1: Expression "it" is not of type ROW</p>
  </blockquote>
  
  <p>If I remove the bottom cross join and the column that references it, the query works fine, so there's something I'm doing wrong in trying to unpack the JSON data for the array of string within the array of struct. Any tips? </p>