CODEWITHSUNDEEP
sqlgoogle-bigquery
Garrett
Garrett

Reputation: 401

How to order SQL query by largest string array sizes?

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

Upvotes: 0

Views: 989

Answers (1)

Manish Kumar
Manish Kumar

Reputation: 192

Try the below Query:-

SELECT  bucketTitle, bucketIds
FROM table 
ORDER BY ARRAY_LENGTH(bucketIds) DESC
LIMIT 100

Upvotes: 2

Related Questions