Background
I’m quite new to SQL so apologies if I come off ignorant. After reading through some related threads I am still confused.
I got the data from a Firestore export whose endpoint was a Google Cloud Storage bucket. I created a table from the bucket data in BigQuery.
Question
I want to order the query by the column that has bucketIds
, which is an array<string>
from largest to smallest. And display the corresponding title of that row (bucketTitle
).
SELECT bucketTitle, bucketIds FROM table ORDER BY bucketIds DESC LIMIT 100
Problems / Errors
Error:
ORDER BY does not support expressions of type ARRAY<STRING>
If I use the aggregate function COUNT()
on the array like this:
SELECT bucketTitle, bucketIds FROM table ORDER BY COUNT(bucketIds) DESC LIMIT 100
I receive this error:
The ORDER BY clause only allows aggregation if GROUP BY or SELECT list aggregation is present at
If I group the query by the title like
SELECT bucketTitle, bucketIds FROM table GROUP BY bucketTitle ORDER BY COUNT(bucketIds) DESC LIMIT 100
Then it throws: SELECT list expression references column bucketIds which is neither grouped nor aggregated at
And if I try to include bucketIds
in the grouping like
GROUP BY bucketTitle, COUNT(bucketIds)
The error states: Aggregate function COUNT not allowed in GROUP BY at
However, you are also not allowed to include columns of type array in GROUP BY
Advertisement
Answer
Try the below Query:-
SELECT bucketTitle, bucketIds FROM table ORDER BY ARRAY_LENGTH(bucketIds) DESC LIMIT 100