Join document inside nested array MongoDB

Question

My first collection, called Scenes looks like this:

{
  sceneId: "id1",
  contentId: "contentId1",
  comments: [{
    comment: "abc",
    userId: "test1"
  },
  {
    comment: "def",
    userId: "test2"
  }]
},
{
  sceneId: "id2",
  contentId: "contentId2",
  comments: [{
    comment: "abc",
    userId: "test1"
  },
  {
    comment: "def",
    userId: "test2"
  }]
}

Any my second collection, called Userdatas, looks like this:

{
  userId: "test1",
  profilPicture: "def.jpg"
},
{
  userId: "test2",
  profilPicture: "abc.jpg"
}

And I want to join them in a way, that I get the following:

{
  sceneId: "id1",
  contentId: "contentId1",
  comments: [{
    comment: "abc",
    userId: "test1",
    profilPicture: "def.jpg"
  },
  {
    comment: "def",
    userId: "test2",
    profilPicture: "abc.jpg"
  }]
},
{
  sceneId: "id2",
  contentId: "contentId2",
  comments: [{
    comment: "abc",
    userId: "test1",
    profilPicture: "def.jpg"
  },
  {
    comment: "def",
    userId: "test2",
    profilPicture: "abc.jpg"
  }]
}

and I have no idea how to do this, all my previous attempts failed. Help is appreciated! The key problem is, that the $group operator will not show the contentId, when grouping via the sceneId

J.F. · Accepted Answer · 2021-08-17 14:00:19Z

2

You can use this aggregation query:

First $unwind comments array to destructure and join by comments.userId.
Then $lookup that is like a JOIN, to merge userIds between collections generating a object called data.
Destructure $data to get values.
Add profilPicture to the object comments using $set.
$group by sceneId adding all comments into an array.
And use $projection to show only fields you want.

db.Scenes.aggregate([
  {
    "$unwind": "$comments"
  },
  {
    "$lookup": {
      "from": "Userdatas",
      "localField": "comments.userId",
      "foreignField": "userId",
      "as": "data"
    }
  },
  {
    "$unwind": "$data"
  },
  {
    "$set": {
      "comments.profilPicture": "$data.profilPicture"
    }
  },
  {
    "$group": {
      "_id": "$sceneId",
      "comments": {
        "$push": "$comments"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "sceneId": "$_id",
      "comments": 1
    }
  }
])

Example here

answered Aug 17, 2021 at 14:00

J.F.

15.3k10 gold badges41 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Noltibus Over a year ago

I forgot to mention, that the documents in Scenes collection have multiple other attributes that I want to retrieve as well. But when I use the $group operator, the values all disappear. I updated my question accordingly.

J.F. Over a year ago

You can add "contentId": {"$first": "$contentId"} into $group stage like this example

Collectives™ on Stack Overflow

Join document inside nested array MongoDB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related