Nested Aggregation

Hi.
I am trying to create a query to build the same view of the image attached, I am using MongoDB Driver with c#. I tried a lot of things to get this done. I managed to get the list on the right but failed to get the categories and count on the left side. Categories on the left side should be built according to the first aggregation that brings the list. I tried group, and bucket but didn’t manage to get the result I want.
Appreciate your help.

Note: The working version viewed in the image is build using Elasticsearch. And it was a simple process using nested agg. We are moving to MongoDB now and seeking the same result.

Here is my query:

    #region Mongo Magic
                var collection = _database.GetCollection<LeadMongoModel>("Leads");

                var S = Builders<LeadMongoModel>.Sort.Descending(c => c.PublishTime);


                var result = collection.Aggregate().Sort(S);
                
                var categoriesAgg = result.Group(x => x.MainCategories.Select(cc => cc.CategoryId), g => new { Id = g.Key, Count = g.Count() }).ToList();

                var finalResult = result.Skip(0).Limit(10).ToList();

                #endregion

This is how my documents look like:

{"_id":{"$numberInt":"36"},"DomainSystems":null,"rlt_DomainSystem_Id":null,"LinkString":"epoxy-coating-importing-request-from-south-africa-36","Type":{"$numberInt":"0"},"TypeText":"Buy","Credit":{"$numberInt":"10"},"PublishTime":{"$date":{"$numberLong":"1555161729000"}},"rlt_Region_Id":{"$numberInt":"58924"},"NameSurname":"Mogamat Shareef Rhoda","Email":"shareefrhoda@gmail.com","Address":"","WebAddress":"","Fax":"","Phone":"27661507614","Categories":[{"MainCategoryId":{"$numberInt":"12"},"SubCategoryId":{"$numberInt":"251"},"MainCategoryIcon":null,"SubCategoryIcon":"fa fa-asterisk","MainCategoryLocalizations":[{"LangCode":"ar","Name":"مستلزمات انشائية","Slug":"مستلزمات-انشاية"},{"LangCode":"en","Name":"Construction and Building Industry","Slug":"construction-and-building-industry"},{"LangCode":"es","Name":"Materiales de Construcción","Slug":"materiales-de-construccion"},{"LangCode":"fr","Name":"Matériaux de construction","Slug":"materiaux-de-construction"},{"LangCode":"pt","Name":"Materiais de prédio e construção","Slug":"materiais-de-predio-e-construcao"},{"LangCode":"ru","Name":"Строительные материалы","Slug":"строительные-материалы"},{"LangCode":"tr","Name":"Yapı ve İnşaat Malzemeleri","Slug":"yapi-ve-insaat-malzemeleri"}],"SubCategoryLocalizations":[{"LangCode":"ar","Name":"مواد بناء كيمياوية","Slug":"مواد-بناء-كيمياوية"},{"LangCode":"en","Name":"Construction Chemicals","Slug":"construction-chemicals"},{"LangCode":"es","Name":"Productos Químicos de Construcción","Slug":"productos-quimicos-de-construccion"},{"LangCode":"fr","Name":"Produits chimiques pour la construction","Slug":"produits-chimiques-pour-la-construction"},{"LangCode":"pt","Name":"Produtos químicos para construção","Slug":"produtos-quimicos-para-construcao"},{"LangCode":"ru","Name":"Строительная Химия","Slug":"строительная-химия"},{"LangCode":"tr","Name":"Yapı Kimyasalları","Slug":"yapi-kimyasallari"}]},{"MainCategoryId":{"$numberInt":"12"},"SubCategoryId":{"$numberInt":"263"},"MainCategoryIcon":null,"SubCategoryIcon":"fas fa-building","MainCategoryLocalizations":[{"LangCode":"ar","Name":"مستلزمات انشائية","Slug":"مستلزمات-انشاية"},{"LangCode":"en","Name":"Construction and Building Industry","Slug":"construction-and-building-industry"},{"LangCode":"es","Name":"Materiales de Construcción","Slug":"materiales-de-construccion"},{"LangCode":"fr","Name":"Matériaux de construction","Slug":"materiaux-de-construction"},{"LangCode":"pt","Name":"Materiais de prédio e construção","Slug":"materiais-de-predio-e-construcao"},{"LangCode":"ru","Name":"Строительные материалы","Slug":"строительные-материалы"},{"LangCode":"tr","Name":"Yapı ve İnşaat Malzemeleri","Slug":"yapi-ve-insaat-malzemeleri"}],"SubCategoryLocalizations":[{"LangCode":"ar","Name":"اغطية - الارضية","Slug":"اغطية-الارضية"},{"LangCode":"en","Name":"Flooring - Covering","Slug":"flooring-covering"},{"LangCode":"es","Name":"Pisos","Slug":"pisos"},{"LangCode":"fr","Name":"Carrelage","Slug":"carrelage"},{"LangCode":"pt","Name":"Pisos","Slug":"pisos"},{"LangCode":"ru","Name":"Напольные - покрытия","Slug":"напольные-покрытия"},{"LangCode":"tr","Name":"Yer Döşemeleri","Slug":"yer-dosemeleri"}]},{"MainCategoryId":{"$numberInt":"8"},"SubCategoryId":{"$numberInt":"181"},"MainCategoryIcon":null,"SubCategoryIcon":"fa fa-flask","MainCategoryLocalizations":[{"LangCode":"ar","Name":"صناعة كيميائية","Slug":"صناعة-كيمياية"},{"LangCode":"en","Name":"Chemical Industry","Slug":"chemical-industry"},{"LangCode":"es","Name":"Industria Química","Slug":"industria-quimica"},{"LangCode":"fr","Name":"Industrie chimique","Slug":"industrie-chimique"},{"LangCode":"pt","Name":"Indústria Química","Slug":"industria-quimica"},{"LangCode":"ru","Name":"Химическая Промышленность","Slug":"химическая-промышленость"},{"LangCode":"tr","Name":"Kimya Sanayii","Slug":"kimya-sanayii"}],"SubCategoryLocalizations":[{"LangCode":"ar","Name":"دهانات صناعية","Slug":"دهانات-صناعية"},{"LangCode":"en","Name":"Industrial Paints","Slug":"industrial-paints"},{"LangCode":"es","Name":"Pinturas Industriales","Slug":"pinturas-industriales"},{"LangCode":"fr","Name":"Peintures Industrielles","Slug":"peintures-industrielles"},{"LangCode":"pt","Name":"Tintas Industriais","Slug":"tintas-industriais"},{"LangCode":"ru","Name":"Промышленные Краски","Slug":"промышленые-краски"},{"LangCode":"tr","Name":"Endüstriyel Boyalar","Slug":"endustriyel-boyalar"}]}],"Country":[{"LangCode":"tr","Name":"Güney Afrika","Icon":"ZA"},{"LangCode":"en","Name":"South Africa","Icon":"ZA"},{"LangCode":"ar","Name":"جنوب أفريقيا","Icon":"ZA"},{"LangCode":"ru","Name":"ЮАР","Icon":"ZA"},{"LangCode":"es","Name":"Sudáfrica","Icon":"ZA"},{"LangCode":"fr","Name":"Afrique du Sud","Icon":"ZA"},{"LangCode":"pt","Name":"África do Sul","Icon":"ZA"}],"Localizations":[{"LangCode":"en","Title":"Epoxy coating importing request from South Africa","ShortDescription":"Looking for a company that deals in epoxy coating technology","Content":"looking for a company that deals in epoxy coating technology","Keywords":"epoxy,epoxy coating,flooring,floor coating"}],"Documents":[]}

Hello @Forie_Forie This is a use case much better suited for Realm/Device Syncs WebSDK or C# SDK.

Anything Edge/Client Side push for Realm/Device Sync, backend is better for Drivers.

You can also use Atlas Triggers/Functions or the GraphQL API as well.

If you MUST use a driver for your use case etc.

This will help you formulate your aggregations, there is no shortcuts and you REALLY Need to understand how it works, or you’re going to have a lot of growing pains. It’s easier to sit-down and do the 10 hours of training on it, for long-term use.

Thank you, Brock. I went through the links you shared.
I’m actually familiar with most of these concepts and Im practicing them in the project already.
I am just trying to create the left-side filter from the main query. I managed to get the filter result that using unwind and group by. but the query is extremely slow compared to Elastic (150ms in Elastic, 20 SECONDS in Mongo). The document count is not that big as well (600K). I revised the indexes and everything looks good. I am sure I’m not doing it the right way. :confused:

Hi Forie,

That’s more so why I recommended the 8 hour course for aggregations. It really does make a difference in knowledge for a lot of people.

@Forie_Forie
If you have the ability, sign up for the free Developer Support Plan (It’s free trial for a month) and then open a support ticket.

Explain the scenario, and provide a copy of your aggregation. And request by name Adam Harrison for the case, if you need a complex aggregation he can not only figure it out, but in detail explain what it’s doing and where/why it’s bogging down and provide you a better alternative solution.