title: How to cross join unnest a JSON array in Presto
body: <p>Given a table that contains a column of JSON like this:</p>
  
  <pre><code>{"payload":[{"type":"b","value":"9"}, {"type":"a","value":"8"}]}
  {"payload":[{"type":"c","value":"7"}, {"type":"b","value":"3"}]}
  </code></pre>
  
  <p>How can I write a Presto query to give me the average <code>b</code> value across all entries?</p>
  
  <p>So far I think I need to use something like Hive's <a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-explode" rel="noreferrer">lateral view explode</a>, whose equivalent is <a href="https://prestodb.io/docs/current/migration/from-hive.html#use-unnest-to-expand-arrays-and-maps" rel="noreferrer">cross join unnest</a> in Presto.</p>
  
  <p>But I'm stuck on how to write the Presto query for <code>cross join unnest</code>.</p>
  
  <p>How can I use <code>cross join unnest</code> to expand all array elements and select them?</p>