How can i split column and group by in bigquery?

Question

I has a SQL code in Legacy SQL was worked,

but in Standard SQL was wrong,

got the response:

 Grouping by expressions of type ARRAY is not allowed

Is there any way can resolve ?

Here's my SQL code:

  select tag
  from 
  (
    select SPLIT(content_tag, ',') as tag
    from `test.log`
  )
  group by tag

Please add an example of the input data and the expected result — Ary Jazz, Nov 12 '18 at 08:09
Take a look at the following question in stackoverflow, it might help you: [BigQuery: error in SPLIT() returns](https://stackoverflow.com/questions/44828808/bigquery-error-in-split-returns) — mohabbati, Nov 12 '18 at 08:41

score 2 · Accepted Answer · edited Nov 12 '18 at 09:02

2

I think you are missing the [SAFE_OFFSET(1)] in your query, This should work

SELECT SPLIT(content_tag, ',') [SAFE_OFFSET(1)] AS tag
FROM `test.log`
GROUP BY tag

Edited for format code.

edited Nov 12 '18 at 09:02

Inaam ur Rehman

470
1
6
23

answered Nov 12 '18 at 08:45

Tamir Klein

3,514
1
20
38

score 1 · Answer 2 · answered Nov 12 '18 at 12:35

The legacy SQL query that you have provided will implicitly flatten the array returned from the SPLIT function, which is why it works. Using standard SQL, you need to be more explicit, however:

select tag
from `test.log`,
  UNNEST(SPLIT(content_tag, ',')) as tag
group by tag

score 0 · Answer 3 · answered Nov 12 '18 at 12:33

I am guessing you want something like this:

select tag, count(*)
from (select 'a b c' as tags union all
      select 'd c'
     ) `test.log` cross join
     unnest(split(tags, ' ')) tag
group by tag
order by count(*) desc;

This will count the number of tags in the space delimited list of tags.

How can i split column and group by in bigquery?

3 Answers3