Get Data from another collection (string -> ObjectId)

Question

Let's say I have these two collections:

// Members:
{
    "_id":{
        "$oid":"60dca71f0394f430c8ca296d"
    },
    "church":"60dbb265a75a610d90b45c6b",
    "name":"Julio Verne Cerqueira"
},
{
    "_id":{
        "$oid":"60dca71f0394f430c8ca29a8"
    },
    "nome":"Ryan Steel Oliveira",
    "church":"60dbb265a75a610d90b45c6c"
}

And

// Churches
{
    "_id": {
        "$oid": "60dbb265a75a610d90b45c6c"
    },
    "name": "Saint Antoine Hill",
    "active": true
},
{
    "_id": {
        "$oid": "60dbb265a75a610d90b45c6b"
    },
    "name": "Jackeline Hill",
    "active": true
}

And I want to query it and have a result like this:

// Member with Church names
{
    "_id":{
        "$oid":"60dca71f0394f430c8ca296d"
    },
    "church":"Jackeline Hill",
    "name":"Julio Verne Cerqueira"
},
{
    "_id":{
        "$oid":"60dca71f0394f430c8ca29a8"
    },
    "church":"Saint Antoine Hill",
    "nome":"Ryan Steel Oliveira"
}

If I try a Lookup, I have the following Result: (It is getting the entire churches collection).

How would I do the query, so it gives me only the one church that member is related to?

And, if possible, how to Sort the result in alphabetical order by church then by name?

Obs.: MongoDB Version: 4.4.10

Answer 1

There is matching error in the $lookup --> $pipeline --> $match.

It should be:

$match: {
  $expr: {
    $eq: [
      "$_id",
      "$$searchId"
    ]
  }
}

From the provided documents, members to churchies relationship will be 1 to many. Hence, when you join members with churchies via $lookup, the output church will be an array with only one churchies document.

---

Aggregation pipelines:

1. $lookup - Join members collection (by $$searchId) with churchies (by _id).
2. $unwind - Deconstruct church array field to multiple documents.
3. $project - Decorate output document.
4. $sort - Sort by church and name ascending.

db.members.aggregate([
  {
    "$lookup": {
      "from": "churchies",
      "let": {
        searchId: {
          "$toObjectId": "$church"
        }
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $eq: [
                "$_id",
                "$$searchId"
              ]
            }
          }
        },
        {
          $project: {
            name: 1
          }
        }
      ],
      "as": "church"
    }
  },
  {
    "$unwind": "$church"
  },
  {
    $project: {
      _id: 1,
      church: "$church.name",
      name: 1
    }
  },
  {
    "$sort": {
      "church": 1,
      "name": 1
    }
  }
])

Sample Mongo Playground