title: Athena nested Struct Querying - how to query Value_counts in SQL
body: <p>I have a big nested struct in AWS Athena. Here's one column named "petowners" in the table: </p>
  
  <pre><code>{_id=5e6b531a412345e0e86aeae0, status=NotAnalyzed, animalcategories=[{categoryname=mammals, matches=1}, {categoryname=birds, matches=2}, {categoryname= UnknownField, matches=4}], ...many-other-values}
  </code></pre>
  
  <p>I'm looking for:</p>
  
  <ol>
  <li>The equivalent of the python function <code>value_counts</code> in the column.
  Meaning I'm looking for the SQL Athena command that will output for
  this row: <code>[mammals:1, birds:2, UnknownField:4]</code>    </li>
  <li>A way to query aggregation - to create a histogram of total number
  of pets per owner for this <code>row = 7</code></li>
  <li>How many pet owners have an <code>UnknownField</code> in 'animalycategories'</li>
  <li>How many animal types are there in the entire table?</li>
  </ol>