title: Unnesting in SQL (Athena): How to convert array of structs into an array of values plucked from the structs?
body: <p>I am taking samples from a Bayesian statistical model, serializing them with Avro, uploading them to S3, and querying them with Athena.</p>
  
  <p>I need help writing a query that unnests an array in the table.  </p>
  
  <p>The CREATE TABLE query looks like:</p>
  
  <pre><code>CREATE EXTERNAL TABLE `model_posterior`(
    `job_id` bigint,
    `model_id` bigint,
    `parents` array&lt;struct&lt;`feature_name`:string,`feature_value`:bigint, `is_zid`:boolean&gt;&gt;,
    `posterior_samples` struct &lt;`parameter`:string,`is_scaled`:boolean,`samples`:array&lt;double&gt;&gt;)
  </code></pre>
  
  <p>The "samples" array in the "posterior_samples" column is where the samples are stored.  I have managed to unnest the "posterior_samples" struct with the following query:</p>
  
  <pre><code>WITH samples AS (
      SELECT model_id, parents, sample, sample_index
      FROM posterior_db.model_posterior 
      CROSS JOIN UNNEST(posterior_samples.samples) WITH ORDINALITY AS t (sample, sample_index)
      WHERE job_id = 111000020709
  )
  SELECT * FROM samples
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/j46cL.png" rel="noreferrer"><img src="https://i.stack.imgur.com/j46cL.png" alt="enter image description here"></a></p>
  
  <p>Now what I want is to unnest the parents column.  Each record in this column is an array of structs.  I am trying to create a column that just has an array of values for the "feature_value" keys in that array of structs. (The reason why I want an array is that the parents array can have a length > 1).  </p>
  
  <p>In other words for each array in the parents row, I want an array of the same size.  That array should contain only the values of the "feature_value" key from the structs in the original array.</p>
  
  <p>Any advice on how to solve this?</p>
  
  <p>Thanks.</p>