How do I convert this to mongodb aggregate query?-CodePudding

SELECT prop_type , count(prop_type) As no_of_properties from prop_type JOIN prop_for_rent USING (prop_type) GROUP BY prop_type;

prop_type and prop_for_rent are both tables. Then "prop_type" used for the JOIN and GROUP BY is an attribute name in both.

The expected result is for the query to count the number of properties in each property type. And this is the result of the sql version of it Screenshot of sql query result

So i need it to display the same prop_type and then the number of properties in each type in mongodb

CodePudding user response：

db.prop_type.aggregate([
  {
    "$lookup": {
      "from": "prop_for_rent",
      "localField": "prop_type",
      "foreignField": "prop_type",
      "as": "prop_for_rent_docs"
    }
  },
  {
    "$unwind": {
      path: "$prop_for_rent_docs",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    "$group": {
      "_id": "$prop_type",
      "no_of_properties": {
        "$sum": 1
      }
    }
  }
])

mongoplayground