title: Querying nested JSON structures in AWS Athena
body: <p>I got the following format of JSON document with nested structures </p>
  
  <pre><code>{
      "id": "p-1234-2132321-213213213-12312",
      "name": "athena to the rescue",
      "groups": [
          {
              "strategy_group": "anyOf",
              "conditions": [
                  {
                      "strategy_conditions": "anyOf",
                      "entries": [
                          {
                              "c_key": "service",
                              "C_operation": "isOneOf",
                              "C_value": "mambo,bambo,jumbo"
                          },
                          {
                              "c_key": "hostname",
                              "C_operation": "is",
                              "C_value": "lols"
                          }
                      ]
                  }
              ]
          }
      ],
      "tags": [
          "aaa",
          "bbb",
          "ccc"
      ]
  }
  </code></pre>
  
  <p>I have created table in Athena to support it using the following </p>
  
  <pre><code>CREATE EXTERNAL TABLE IF NOT EXISTS filters ( id string, name string, tags array&lt;string&gt;, groups array&lt;struct&lt;
      strategy_group:string,
      conditions:array&lt;struct&lt;
          strategy_conditions:string,
          entries: array&lt;struct&lt;
              c_key:string,
              c_operation:string,
              c_value:string
          &gt;&gt;
      &gt;&gt;
  &gt;&gt; ) row format serde 'org.openx.data.jsonserde.JsonSerDe' location 's3://filterios/policies/';
  </code></pre>
  
  <p>My goal at the moment is to query based on the conditions entries columns as well. I have tried some queries however sql language is not my biggest trade ;) </p>
  
  <p>I got at the moment to this query which gives me entries </p>
  
  <pre><code>select cnds.entries from 
  filters,
  UNNEST(filters.groups) AS t(grps),
  UNNEST(grps.conditions) AS t(cnds)
  </code></pre>
  
  <p>However since this is complex array it gives me some headeache what would be the proper way to query. </p>
  
  <p>Any hints appreciated! </p>
  
  <p>thanks
  R</p>