title: Extract array items as a view - AWS Athena
body: <p>I am trying to select all elements from an array that exists in one of the fields in Athena, for example:</p>
  
  <pre><code>{
      id: "1",
      name: "bla",
      array: [{
          val1: "2",
          val2: "2"
      }, {
          val1: "3",
          val2: "4"
      }]
  }
  
  
  {
      id: "3",
      name: "bla bla",
      array: [{
          val1: "5",
          val2: "6"
      }, {
          val1: "7",
          val2: "8"
      }]
  }
  </code></pre>
  
  <p>I am trying to create a view that selects all the elements from the inner array, the result would be:</p>
  
  <pre><code>+----+------+------+
  | id | val1 | val2 |
  +----+------+------+
  | 1  | 2    | 2    |
  +----+------+------+
  | 1  | 3    | 4    |
  +----+------+------+
  | 2  | 5    | 6    |
  +----+------+------+
  | 2  | 7    | 8    |
  +----+------+------+
  </code></pre>
  
  <p>What is the query to produce such an output?</p>
  
  <p>The actual file will be one item per line, like this:</p>
  
  <p>{
      id: "1",
      name: "bla",
      array: [{
          val1: "2",
          val2: "2"
      }, {
          val1: "3",
          val2: "4"
      }]
  }</p>
  
  <p>{
      id: "3",
      name: "bla bla",
      array: [{
          val1: "5",
          val2: "6"
      }, {
          val1: "7",
          val2: "8"
      }]
  }</p>
  
  <p>The DDL to create the table looks like this:</p>
  
  <pre><code>CREATE EXTERNAL TABLE all (
    id STRING,
    name STRING,
    array ARRAY&lt;
                struct&lt;
                 val1:STRING,
                 val2:STRING
             &gt;    &gt;          
    )
  ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
  LOCATION 's3://abc/def'
  </code></pre>