how to count repetitive values in array GBQ?

Question

How can I count repetitive values for each array in Google-Big-Query?

DATA:

WITH temp AS (SELECT 1 id, ["a","b", "a"] name )
SELECT * FROM temp

How to get:

1 | name | count|
  |  a   |   2  |
  |  b   |   1  |

Answer 1

Consider below approach

select id, array(
    select as struct name, count(1) `count`
    from t.name name
    group by name
  ) stats
from temp t             

with output