Attaching an object inside an array inside of a document in a different collection to an object in c-CodePudding

I have 2 collections in my database. I will simplify for the purpose of the question.

Users collection:

{
    _id: ObjectId(friend1Id),
    username: 'john',
    friends: [friendId2, friendId3]
}
{
    _id: ObjectId(friend2Id),
    username: 'lucy',
    friends: [friendId1]
}
{
    _id: ObjectId(friend3Id),
    username: 'earl',
    friends: [friendId1]
}

Servers collection:

{
_id: s101,
ownder: 'john'
name: 'name'
channels: [
    {
         _id: ObjectId('c101'),
         with: friendId2
    },
    {
         _id: ObjectId('c102'),
         with: friendId3
    },
]
}

I tried a lot of variations with $match, $lookup and $unwind but can't seem to get the right combination to pull it off. I am trying to get all of john's friends in an array, but I want to attach their corresponding channel _id from the server collection.

Like this:

[
 {
    _id: friend2Id,
    username: 'lucy',
    friends: [friendId1],
    channel: c101
 }

 {
    _id: friend3Id,
    username: 'earl',
    friends: [friendId1],
    channel: 'c102'
 }
]

As you can see, I want to attach the Id of the channel john and his friend share in john's server. I can get the friends array with

collection.find({ friends: friend1Id }).toArray()

I tried running this aggregation I found in this question that tries to solve a similar problem.

I tried this:


let friends = await collection.aggregate([
            {
                $match: { friends: userId }
            },
            {
                $addFields: {
                    convertedId: { $toString: "$_id" }
                }
            },
            {
                $lookup:
                {
                    from: 'server',
                    let: { 'friend_id': '$convertedId' },
                    pipeline: [
                        {
                            $unwind: '$channels'
                        },
                        {
                            $match: { $expr: { $eq: ['$channels.with', '$$friend_id'] } }
                        },
                    ],
                    as: 'channel'
                }
            },
        ]).toArray()

But this just slaps the entire server object onto the friend as a value for the channel key.

How do I achieve the following result with aggregation?

{
    _id: friend3Id,
    username: 'earl',
    friends: [friendId1],
    channel: 'c102'
 }

CodePudding user response：

One option is to start from the server and $unwind the channels. Then just merge the objects:

db.servers.aggregate([
  {$match: {_id: "s101"}},
  {$project: {channels: 1, _id: 0}},
  {$unwind: "$channels"},
  {$lookup: {
      from: "users",
      let: {"friend_id": "$channels.with"},
      pipeline: [{$match: {$expr: {$eq: [{$toString: "$_id"}, "$$friend_id"]}}}],
      as: "friend"
  }},
  {$replaceRoot: {
      newRoot: {$mergeObjects: [{channel: "$channels._id"}, {$first: "$friend"}]}
  }}
])

See how it works on the playground example