How can I count the number of completed houses designed by a specific architect in MongoDB? I have the next two collections, "plans" and "houses".
Where the only relationship between houses and plans is that houses have the id of a given plan.
Is there a way to do this in MongoDB with just one query?

plans
{
    _id: ObjectId("6388024d0dfd27246fb47a5f")
    "hight": 10,
    "arquitec": "Aneesa Wade",
},
{
    _id: ObjectId("1188024d0dfd27246fb4711f")
    "hight": 50,
    "arquitec": "Smith Stone",
}
houses
{
    _id: ObjectId
    "plansId": "6388024d0dfd27246fb47a5f" -> string,
    "status": "under construction",
},
{
    _id: ObjectId
    "plansId": "6388024d0dfd27246fb47a5f" -> string,
    "status": "completed",
}

What I tried was to use mongo aggregations while using $match and $lookup.
The "idea" with clear errors would be something like this.

db.houses.aggregate([
    {"$match": {"status": "completed"}},
    {
        "$lookup": {
            "from": "plans",
            "pipeline": [
                { 
                    "$match": { 
                        "$expr": { 
                            "$and": [
                                { "$eq": [ "houses.plansId", { "$toString": "$plans._id" }]},
                                { "plans.arquitec" : "Smith Stone" },                                
                            ]
                        } 
                    } 
                },
            ],
        }
    }

CodePudding user response：

If it's a single join condition, simply do a project to object ID to avoid any complicated lookup pipelines.

Example playground - https://mongoplayground.net/p/gaqxZ7SzDTg

db.houses.aggregate([
  {
    $match: {
      status: "completed"
    }
  },
  {
    $project: {
      _id: 1,
      plansId: 1,
      status: 1,
      plans_id: {
        $toObjectId: "$plansId"
      }
    }
  },
  {
    $lookup: {
      from: "plans",
      localField: "plans_id",
      foreignField: "_id",
      as: "plan"
    }
  },
  {
    $project: {
      _id: 1,
      plansId: 1,
      status: 1,
      plan: {
        $first: "$plan"
      }
    }
  },
  {
    $match: {
      "plan.arquitec": "Some One"
    }
  }
])

Update: As per OP comment, added additional match stage for filtering the final result based on the lookup response.