CODEWITHSUNDEEP
sqlgoogle-bigquery
monckeyyL
monckeyyL

Reputation: 111

COUNT() AND GROUP BY in BIGQUERY

I have tried to use UNNEST in BigQuery but the output looks like this

------------------------------------------
|Row |    Name         |   user_id       ｜
------------------------------------------
| 1  |    STAY         |      1          ｜
-    -------------------
|    |LOVE YOURSELF    |                 ｜
-    -------------------
|    |  GREEN BOOK     |                 ｜
-    -------------------
|    |CRAZY IN LOVE    |                 ｜
------------------------------------------

And my query is :

select user_id,
       array(select json_extract_scalar(x, '$.Name') from 
       unnest(json_extract_array(json_col, '$.info.music') x) NAME
from data

I expected the table to be as following because I wanted to count the number of users who listen to each song. Does anyone have any suggestions? Could I actually count using the above table?

------------------------------------------
|Row |    Name         |   user_id       ｜
------------------------------------------
| 1  |    STAY         |      1          ｜
------------------------------------------
| 2  |LOVE YOURSELF    |      1          ｜
------------------------------------------
| 3  |  GREEN BOOK     |      1          ｜
------------------------------------------
| 4  |CRAZY IN LOVE    |      1          ｜
------------------------------------------

Upvotes: 0

Views: 1259

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

Consider below

select name, user_id from (
  select user_id,
    array(select json_extract_scalar(x, '$.Name') from 
    unnest(json_extract_array(json_col, '$.info.music')) x) NAME
  from data
) t, t.name name

with output

enter image description here

Upvotes: 1

Related Questions