title: Equivalent of Hive Lateral view outer Explode in Athena (Presto) CROSS JOIN UNNEST
body: <p>We are trying to create an Unnest view in Athena which is equivalent to Hive lateral view for JSON data which has array fields in it then if the unnest is null then parent ky is getting dropped.</p>
  
  <p>Below are the sample JSONs we tried to create a view on. </p>
  
  <pre><code>{"root":{"colA":"1","colB":["a","b","c"]}}
  {"root":{"colA":"2"}}
  </code></pre>
  
  <p>The output for above data in Hive view is as below:</p>
  
  <pre><code>+----------------------+----------------------+--+ 
  
  | test_lateral_v.cola  | test_lateral_v.colb  |    
  +----------------------+----------------------+--+ 
  
  | 1                    | a                    |    
  | 1                    | b                     
  | 1                    | c                    |    
  | 2                    | NULL                 |    
  +----------------------+----------------------+--+ 
  </code></pre>
  
  <p>But when we are trying to create the view in Athena with CROSS JOIN UNNEST below is the output:</p>
  
  <p>cola  colb</p>
  
  <pre><code>1   a
  
  1   b
  
  1   c
  </code></pre>
  
  <p>If the JSON data does not have the values for the field which we have created the UNNEST on, that row is getting eliminated from the output, whereas hive gives that row as well with NULL value for the corresponding missing value.</p>
  
  <p>/<em>DDLs used in hive</em>/</p>
  
  <pre><code>create    external    table    if    not    exists    test_lateral(
  root    struct&lt;
   colA:    string,
   colB:    array&lt;
    string
    &gt;
   &gt;
   )
  ROW    FORMAT    SERDE    'org.apache.hive.hcatalog.data.JsonSerDe'
  Stored    as    textfile 
  location    "&lt;hdfs_location&gt;";
  
  create view test_lateral_v 
  (colA,colB)
  as select
  root.colA,
  alias
  from test_lateral
  lateral view outer explode (root.colB) t as alias;
  </code></pre>
  
  <hr>
  
  <p>/<em>DDLs used for athena</em>/</p>
  
  <pre><code>create external table if not exists test_lateral(
  root struct&lt;
   colA: string,
   colB: array&lt;
    string
    &gt;
   &gt;
   )
  ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
  Stored as textfile 
  
  location "&lt;s3_location&gt;";
  
  create view test_lateral_v 
  as select
  root.colA,
  alias as colB
  from test_lateral
  cross join unnest (root.colB) as t (alias);
  </code></pre>