CODEWITHSUNDEEP
sqlgoogle-bigquerylegacy-sql
Allen
Allen

Reputation: 29

How can i split column and group by in bigquery?

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

Upvotes: 0

Views: 1464

Answers (3)

Elliott Brossard
Elliott Brossard

Reputation: 33765

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 0

Tamir Klein
Tamir Klein

Reputation: 3642

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.

Upvotes: 2

Related Questions