Count Groupby From String (array) in BigQuery

Question

I have this table

Schema: name: string interests: string

name  | interests
user1 | culture, travel 
user2 | soccer, studying, soccer 
user3 | tennis, science, soccer, culture

How to count group by interests so that it resulted with:

interests | count
culture   | 2
travel    | 1
soccer    | 3
... ...

why not normalize your table and avoid splitting the column, which is time consuming see also https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — nbk, Nov 07 '22 at 13:38

score 1 · Accepted Answer · answered Nov 07 '22 at 16:14

With a given table of string and array

CREATE TABLE `Dataset.ClientInterest` 
(
  ClientName string,
  Interest Array<string>
)

and a dataset of

insert into `Dataset.ClientInterest` values ('User1', [ 'culture', 'travel']),
                                               ('User2', [ 'soccer','studying','travel']),
                                               ('User3', [ 'tennis', 'science', 'soccer', 'culture' ])

Then unnest the array in BigQuery to allow group by

SELECT   interest, 
         COUNT(ClientName) 
FROM    `Dataset.ClientInterest` c, 
         UNNEST(Interest) interest 
GROUP BY interest

resulting in

Count Groupby From String (array) in BigQuery

1 Answers1