CODEWITHSUNDEEP
google-bigquery
ronencozen
ronencozen

Reputation: 2201

BigQuery - how to sort query results based on array element

I run the following query:

with x AS(
select agent_id, array_agg(struct(price_range as level, sales)) as price_range
from(select agent_id, price_range, count(*) sales
     from (select 1 as agent_id, 'b' as price_range
           union all
           select 1 as agent_id, 'a' as price_range
           union all
           select 1 as agent_id, 'c' as price_range
           union all
           select 2 as agent_id, 'a' as price_range)
     group by 1,2)
     group by 1)

select * from x

I would like to get the results sorted first by the agent_id and then by price_range level.
The thing is that if i unnest() the array first i get a cross join results.

before

after

Upvotes: 1

Views: 1526

Answers (2)

ronencozen
ronencozen

Reputation: 2201

If I sort the sub query results before constructing the array, than i get what i wanted.

with x AS(
select agent_id, array_agg(struct(price_range as level, sales)) as price_range
from(select agent_id, price_range, count(*) sales
     from (select 1 as agent_id, 'b' as price_range
           union all
           select 1 as agent_id, 'a' as price_range
           union all
           select 1 as agent_id, 'c' as price_range
           union all
           select 2 as agent_id, 'a' as price_range)
     group by 1,2
     order by 1,2) -- this additional line solved the issue
     group by 1)

select * from x

Upvotes: 1

Martin Weitzmann
Martin Weitzmann

Reputation: 4746

You can use a subquery in your order by to aggregate the array to value that helps you sort the row. In this example I use a sorting + limit 1 on the array to sort outside of the array.

with x AS(
select agent_id, array_agg(struct(price_range as level, sales)) as price_range
from(select agent_id, price_range, count(*) sales
     from (select 1 as agent_id, 'b' as price_range
           union all
           select 1 as agent_id, 'a' as price_range
           union all
           select 1 as agent_id, 'c' as price_range
           union all
           select 2 as agent_id, 'a' as price_range)
     group by 1,2)
     group by 1)

select * 
from x
order by (select level from unnest(price_range) order by level desc limit 1) asc
-- same as
-- order by (select max(level) from unnest(price_range)) asc

You can also sort by counting elements in the array or do some other aggregation

with x AS(
select agent_id, array_agg(struct(price_range as level, sales)) as price_range
from(select agent_id, price_range, count(*) sales
     from (select 1 as agent_id, 'b' as price_range
           union all
           select 1 as agent_id, 'a' as price_range
           union all
           select 1 as agent_id, 'c' as price_range
           union all
           select 2 as agent_id, 'a' as price_range)
     group by 1,2)
     group by 1)

select * 
from x
order by (select COUNT(*) from unnest(price_range)) asc
-- same as:
-- order by array_length(price_range) asc

Upvotes: 0

Related Questions