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":[]}