Summarize array values from column

Question

My job has tasked me with summarizing values of multiple arrays and I've reached a gap in my knowledge. The insight and assistance from this group is greatly appreciated.

The challenge:

I have an array of domain TLDs in each row within a single column BigQuery table. I would like to group by each TLD and return a total count by each TLD as a new table.

["biz","us","international","eu","com","co","world","us","international","eu","co","biz"]
["com","co","world"]

Response

**TLD_Name**
biz 2
us 2
international 2
eu 2
com 2
co 3
world 1

Thanks in advance for the help.

Elliott Brossard · Accepted Answer · 2017-12-18 16:31:33Z

2

Supposing that the array column is named tlds, you can run the following standard SQL query:

SELECT
  tld AS TLD_Name,
  COUNT(*) AS count
FROM YourTable
CROSS JOIN UNNEST(tlds) AS tld
GROUP BY tld;

This has the effect of "flattening" the array and getting a count associated with each TLD.

answered Dec 18, 2017 at 16:31

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthew Barrowclough Over a year ago

Very helpful. Thanks for the explanation on "flattening". This is the context I was missing.

Mikhail Berlyant · Accepted Answer · 2017-12-18 18:27:15Z

1

In case if tld values in each row are highly repeatable and you have really large number of rows - below might provide a little optimization by first combining/aggregating tld counts inside each row and then summarizing on whole table level (for BigQuery Standard SQL)

#standardSQL
WITH `yourproject.yourdataset.yourtable` AS (
  SELECT ["biz","us","international","eu","com","co","world","us","international","eu","co","biz"] tlds UNION ALL
  SELECT ["com","co","world","biz"]   
)
SELECT
  tld_count.tld AS tld,
  SUM(tld_count.cnt) AS cnt
FROM `yourproject.yourdataset.yourtable`,
UNNEST(ARRAY(SELECT AS STRUCT tld, COUNT(*) AS cnt FROM UNNEST(tlds) AS tld GROUP BY tld)) AS tld_count
GROUP BY tld

answered Dec 18, 2017 at 18:27

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

2 Comments

Matthew Barrowclough Over a year ago

Thank you. This is helpful as we scale up and maximize efficiency as our data is highly repeatable.

Mikhail Berlyant Over a year ago

if you find it helpful - please consider voting up :o)

Collectives™ on Stack Overflow

Summarize array values from column

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related