Filter $group $count from aggregate


i’m trying to create a Stacked column chart from 2 differents collection linked by a $lookup.
I have a “deliveries” collection containing a “insert_at” (a datetime) and an ObjectId field name “address” related to another collection “addresses” containg address datas and espacially the city name.
I would like to have a chart counting the occurrences number of city by month. I already succeed to create this chart by adding a lookup field and adding a new series with the “city” field from the lookup.
What i can’t do is a descending sort of the number of occurrences, and only display the top 20

I tried to directly put the good aggregation pipeline in the query bar, but of course, Mongo tell me that i can’t use $lookup in the query bar.

Does anyone know how to perform this kind of query ?

Best regards

Hi @Nicolas_LERDA -

Sorry to hear you’re having issues. We don’t allow $lookup or $unionWith in the query bar as that could be used to bypass the data source permissions. If Lookup Fields aren’t an option for you, you can create a Charts View on the Data Sources page that includes the full pipeline you need.