Need to convert this SQL query to MongoDB-CodePudding

I am new to MongoDB. I need to convert this SQL code to MongoDB

select TOP 5 r.regionName, COUNT(c.RegionID)
from region as r,
     company as c
where c.RegionID = r._id
group by r.regionName
order by COUNT(c.RegionID) DESC;

CodePudding user response：

Option 1. You can use the aggregation framework with $lookup, $group, $project , $sort and $limit stages, but this seems like a wrong approach since the true power to change relation database with mongoDB is the denormalization and avoidance of join ($lookup) like queries.

Option 2. You convert your multi-table relational database schema to document model and proceed with simple $group, $project, $sort and $limit stage aggregation query for the above task.

Since you have not provided any mongodb document examples it is hard to provide how your queries will look like ...

CodePudding user response：

Despite of my comment I try to give a translation (not tested):

db.region.aggregate([
{
   $lookup: // left outer join collections
     {
       from: "company",
       localField: "_id",
       foreignField: "RegionID",
       as: "c"
     }
},
{ $match: { c: { $ne: [] } } }, // remove non-matching documents (i.e. INNER JOIN)
{ $group: { _id: "$regionName", regions: { $addToSet: { "$c.RegionID" } } } }, // group and get distinct regions
{ $project: { regionName: "$_id", count: { $size: "$regions" } , _id: 0} } // some cosmetic and count
{ $sort: { regionName: 1 } }, // order result
{ $limit: 5 } // limit number or returned documents
])