title: How do I count number of items per tag in SQL, when tags is an array of strings?
body: <p>I have a table that looks like this:
  <code>ItemID, Tags</code></p>
  <p>ItemID is a string, Tags is an array of strings.</p>
  <p>The DB engine is Presto: <a href="https://prestodb.io/docs/0.172/index.html" rel="nofollow noreferrer">https://prestodb.io/docs/0.172/index.html</a></p>
  <p>I have to return a count of ItemIDs for each tag but I'm kind of getting stuck. My first approach was to extract the Tags and create an unique list and then I was hoping that through some join magic I'd be able to GROUP BY and then count.</p>
  <p>How should I approach this? I don't have a lot of experience with SQL arrays </p>
  <p>I also can't figure out how to use UNNEST to create rows and then a table out of this:</p>
  <pre><code>SELECT filter(flatten(array_agg(split(tags, ','))), x -&gt; x != '')
  FROM my_items
  </code></pre>
  <p>This returns an array of unique tags and apparently with UNNEST this can be turned into a set of rows but I've had no luck so far converting it into that. Tried stuff like:</p>
  <pre><code>SELECT UNNEST(above subquery) FROM MY_ITEMS
  </code></pre>