title: How to work with json arrays in AWS athena
body: <p>I am new to athena and am currently working on reading files from s3 and presenting them as table.</p>
  <p>Below is how individual files look like in s3 buckets.</p>
  <p>sample.json</p>
  <pre><code>[
      {
      &quot;name&quot;:&quot;John&quot;,
      &quot;age&quot;:29,
      &quot;salary&quot;:300
      },
      {
      &quot;name&quot;:&quot;Mary&quot;,
      &quot;age&quot;:25,
      &quot;salary&quot;:500
      }
  ]
  </code></pre>
  <p>I tried creating a table with this data but I am not able to get it in the desire format. I would like to  get each dictionary to take up one row. The table should have three columns (Name, Age, Salary).</p>
  <p>Tried below but it's putting entire content under data column as it is.</p>
  <pre><code>CREATE EXTERNAL TABLE IF NOT EXISTS test(
                     `data` string
                   )
                   LOCATION 's3://somelocation/'
                   TBLPROPERTIES ('has_encrypted_data'='false');
  </code></pre>
  <p>output</p>
  <pre><code>data
  -----
  [{&quot;name&quot;:&quot;John&quot;,...},{{&quot;name&quot;:&quot;Mary&quot;,...}]
  </code></pre>
  <p>Referred <a href="https://stackoverflow.com/questions/61969182/extract-json-in-array-in-aws-athena">this</a> but didn't reach anywhere.</p>
  <p>Is there an ideal way to do this ? FYI, the number of dict present in each file may vary and is not at all fixed. So I am looking for  a dynamic solution which works good if there is just one dict and if there are 10 in each file in s3.</p>