How do I count number of items per tag in SQL, when tags is an array of strings?

Question

I have a table that looks like this: ItemID, Tags

ItemID is a string, Tags is an array of strings.

The DB engine is Presto: https://prestodb.io/docs/0.172/index.html

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.

How should I approach this? I don't have a lot of experience with SQL arrays

I also can't figure out how to use UNNEST to create rows and then a table out of this:

SELECT filter(flatten(array_agg(split(tags, ','))), x -> x != '')
FROM my_items

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:

SELECT UNNEST(above subquery) FROM MY_ITEMS

user_1144035 · Answer

I think you want an unnest() and aggregation:

select t.tag, count(*)
from my_items i cross join
     unnest(split(i.tags, ',')) t(tag)
group by t.tag;

user_14095853 · Answer

Not very sure because I couldn't try it on my Presto (yet). But cardinality function might work.

cardinality(x) → bigint -- Returns the cardinality (size) of the array x.

From Presto docs here - https://prestodb.io/docs/current/functions/array.html

How do I count number of items per tag in SQL, when tags is an array of strings?

2 Answers