BigQuery: Query to GroupBy Array Column

Question

I have (2) columns in BigQuery table:
1. url
2. tags

URL is a single value, and TAGS is an array(example below):

row | URL   &nbsp| TAGS
1      | x.com | donkey
                     | donkey
                     | lives
                     | here

How can I group by TAGS array in BigQuery?

What's the trick to get the following query working?

SELECT TAGS FROM `URL_TAGS_TABLE` 
group by unnest(TAGS)

I have tried group by TO_JSON_STRING but it does not give me the desired results

I'd like to see the following output
x.com | donkey | count 2
x.com | lives | count 1
x.com | here | count 1

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
   SELECT 'x.com' url, ['donkey','donkey','lives','here'] tags UNION ALL
   SELECT 'y.com' url, ['abc','xyz','xyz','xyz'] tags 
)
SELECT url, tag, COUNT(1) AS `count`
FROM `project.dataset.table`, UNNEST(tags) tag
GROUP BY url, tag   

with result

Row url     tag     count    
1   x.com   donkey  2    
2   x.com   lives   1    
3   x.com   here    1    
4   y.com   abc     1    
5   y.com   xyz     3