MongoDB aggregation lookup additional field from localField to result

Question

I have the following collections in MongoDB (mongoplayground) [characters,guilds]

I want to make a $lookup, that will add rank field to the resulting document, like that:

    "members_t": [
      {
        "_id": ObjectId("5a934e000102030405000000"),
        "level": 20,
        "name": "test1",
        "rank": 1
      },
      {
        "_id": ObjectId("5a934e000102030405000001"),
        "level": 40,
        "name": "test2",
        "rank": 2
      }
    ]

old $lookup syntax can't help me with that, but the following query with the new syntax returns me an empty array in tested field (even without $addFields stage):

  {
    $lookup: {
      from: "characters",
      let: {
        members_name: "$members.name",
        rank: "$members.rank"
      },
      pipeline: [
        {
          $match: {
            name: "$$members_name"
          }
        }
      ],
      as: "tested"
    }
  }

So, is there any option to add an additional field after $lookup stage or not?

(Mongo -v 4.2.3, so the problem is not related with new syntax support)

Answer 1

You were almost close to solving it. Since the members are an array, you need to pass it through $lookup and then conditionally join it per each character with $reduce (You can do this with $filter as well, but then we need aditional stages).

Note: Explanation why we need to use $expr inside $lookup pipeline.

Try this one:

db.guilds.aggregate([
  {
    $lookup: {
      from: "characters",
      let: {
        members: "$members"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$name",
                "$$members.name"
              ]
            }
          }
        },
        {
          $addFields: {
            rank: {
              $reduce: {
                input: "$$members",
                initialValue: null,
                in: {
                  $cond: [
                    {
                      $eq: [
                        "$$this.name",
                        "$name"
                      ]
                    },
                    "$$this.rank",
                    "$$value"
                  ]
                }
              }
            }
          }
        }
      ],
      as: "members_t"
    }
  }
])

MongoPlayground