Attaching an object inside an array inside of a document in a different collection to an object in current collection with mongodb aggregate?

Question

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'
 }

score 0 · Accepted Answer · answered Nov 07 '22 at 12:56

0

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

answered Nov 07 '22 at 12:56

nimrod serok

14,151
2
11
33

Thank you for your answer. It produces the correct result, but I am running the aggregate function on the **users** collection, as a lot of components of my project depend on it. Is there a way to do the same only for the **users** collection? – Boris Ryjkov Nov 07 '22 at 13:09
sure, but how do you want to map the document of user with username: 'john', to the server? Do you want to map `username` to `ownder`? – nimrod serok Nov 07 '22 at 13:29
If so you will need to lookup twice... – nimrod serok Nov 07 '22 at 13:37
There's an OwnerId to each server instead of the owner 'name' (john). If the user's _id is ObjectId(friend1Id) then the servers OwnderId field is 'friend1Id'. Is doing lookup twice a bad thing? – Boris Ryjkov Nov 07 '22 at 15:02
It is less efficient – nimrod serok Nov 07 '22 at 15:35
Oh, I see. Thank you! It's my first project so I'm not very concerned with efficiency. I've been trying to figure out how to do that for the past hour. Can you help me out with this? Where would the first and second lookups go? At what order? – Boris Ryjkov Nov 07 '22 at 15:40
For example, you can do something like [this](https://mongoplayground.net/p/nBQ8KmTNHV1). There are many options and variations here, this is one of them – nimrod serok Nov 07 '22 at 15:56

Attaching an object inside an array inside of a document in a different collection to an object in current collection with mongodb aggregate?

1 Answers1