title: Aggregating on an array field
body: <p>I have the following data:</p>
  <pre><code>movie (string)         genres (string[])
  &quot;titanic&quot;              [&quot;romance&quot;, &quot;historical&quot;, &quot;drama&quot;]
  &quot;spider-man&quot;           [&quot;sci-fi&quot;, &quot;action&quot;]
  &quot;casablanca&quot;           [&quot;romance&quot;, &quot;classic&quot;]
  </code></pre>
  <p>Is there a &quot;standard&quot; way -- at least conceptually speaking -- to aggregate on an array field? For example, doing something like:</p>
  <pre><code>SELECT genres, count(*) GROUP BY genres ORDER BY count(*) DESC, genres
  </code></pre>
  <p>If seems to me the result should be something like:</p>
  <pre><code>genres         count
  &quot;romance&quot;      2
  &quot;action&quot;       1
  &quot;classic&quot;      1
  &quot;drama&quot;        1
  &quot;historical&quot;   1
  &quot;sci-fi&quot;       1
  </code></pre>
  <p>Is this how most database engines do aggregating on an array field? I'd be interested to see some examples of how aggregations would be done in that engine.</p>
  <p>Usually when I've done it I get complains and have to manually make this conversion, for example in pandas:</p>
  <pre><code>df=pd.DataFrame({'movie':['titanic', 'spider-man', 'casablanca'], 'genres': [['romance', 'historical', 'drama'], ['sci-fi', 'action'],['romance','classic']]})
  df.groupby('genres').first() # will error, or converting to tuple will not unnest the array
  </code></pre>