title: AWS Athena - Querying JSON - Searching for Values
body: <p>I have nested JSON files on S3 and am trying to query them with Athena.</p>
  <p>However, I am having problems to query the nested JSON values.</p>
  <p>My JSON file looks like this:</p>
  <pre><code> {
    &quot;id&quot;: &quot;17842007980192959&quot;,
    &quot;acount_id&quot;: &quot;17841401243773780&quot;,
    &quot;stats&quot;: [
      {
        &quot;name&quot;: &quot;engagement&quot;,
        &quot;period&quot;: &quot;lifetime&quot;,
        &quot;values&quot;: [
          {
            &quot;value&quot;: 374
          }
        ],
        &quot;title&quot;: &quot;Engagement&quot;,
        &quot;description&quot;: &quot;Total number of likes and comments on the media object&quot;,
        &quot;id&quot;: &quot;17842007980192959/insights/engagement/lifetime&quot;
      },
      {
        &quot;name&quot;: &quot;impressions&quot;,
        &quot;period&quot;: &quot;lifetime&quot;,
        &quot;values&quot;: [
          {
            &quot;value&quot;: 11125
          }
        ],
        &quot;title&quot;: &quot;Impressions&quot;,
        &quot;description&quot;: &quot;Total number of times the media object has been seen&quot;,
        &quot;id&quot;: &quot;17842007980192959/insights/impressions/lifetime&quot;
      },
      {
        &quot;name&quot;: &quot;reach&quot;,
        &quot;period&quot;: &quot;lifetime&quot;,
        &quot;values&quot;: [
          {
            &quot;value&quot;: 8223
          }
        ],
        &quot;title&quot;: &quot;Reach&quot;,
        &quot;description&quot;: &quot;Total number of unique accounts that have seen the media object&quot;,
        &quot;id&quot;: &quot;17842007980192959/insights/reach/lifetime&quot;
      },
      {
        &quot;name&quot;: &quot;saved&quot;,
        &quot;period&quot;: &quot;lifetime&quot;,
        &quot;values&quot;: [
          {
            &quot;value&quot;: 0
          }
        ],
        &quot;title&quot;: &quot;Saved&quot;,
        &quot;description&quot;: &quot;Total number of unique accounts that have saved the media object&quot;,
        &quot;id&quot;: &quot;17842007980192959/insights/saved/lifetime&quot;
      }
    ],
    &quot;import_date&quot;: &quot;2017-12-04&quot;
  }
  </code></pre>
  <p>What I'm trying to do is to query the &quot;stats&quot; field value where name=impressions.</p>
  <p>So ideally something like:</p>
  <pre><code>SELECT id, account_id, stats.values.value WHERE stats.name='engagement'
  </code></pre>
  <p>AWS example: <a href="https://docs.aws.amazon.com/athena/latest/ug/searching-for-values.html" rel="nofollow noreferrer">https://docs.aws.amazon.com/athena/latest/ug/searching-for-values.html</a></p>
  <p>Any help would be appreciated.</p>