How to $lookup/populate an embedded document that is inside an array?

Question

Below is how my schema is looking like.

const CommentSchema = new mongoose.Schema({
    commentText:{
        type:String,
        required: true
    },
    arrayOfReplies: [{  
        replyText:{
            type:String,
            required: true
        },
        replier: [{
            type: mongoose.Schema.Types.ObjectId,
            ref: 'User',
            required: true,
        }],
    }],
});

How can I get query results that look like below:

[    
    {    
        commentText: 'comment text',
        arrayOfReplies: [
            {
                replyText: 'replyText',
                replier: {
                    username:"username"
                    bio: 'bio'
                }
            }
        ]
    }
]

I am trying to populate the replier field inside the array arrayOfReplies. I have tried several variations of the aggregation query below. The ones that have come close to what I am trying to achieve have one short-coming. The comments that do not have replies have an arrayOfReplies array that has an empty object. I.e arrayOfReplies: [{}], essentially meaning that the array is not empty.

I have tried using add fields, $mergeObjects among other pipeline operators but to no avail.

How to $lookup/populate the replier document that is inside the arrayOfReplies array?

Below is a template of the main part of my aggregation query, minus trying populate the replier document.

Comment.aggregate([
    {$unwind: {"path": '$arrayOfReplies', "preserveNullAndEmptyArrays": true }},
    {$lookup:{from:"users",localField:"$arrayOfReplies.replier",foreignField:"_id",as:"replier"}},
    {$unwind: {"path": "$replier", "preserveNullAndEmptyArrays": true }},

    {$group: {
        _id : '$_id',
        commentText:{$first: '$commentText'},
        userWhoPostedThisComment:{$first: '$userWhoPostedThisComment'},
        arrayOfReplies: {$push: '$arrayOfReplies' },
    }},

SuleymanSah · Answer 1 · 2020-03-15T17:38:12.953

You can use the following aggregate:

Playground

Comment.aggregate([
  {
    $unwind: {
      "path": "$arrayOfReplies",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    $lookup: {
      from: "users",
      localField: "arrayOfReplies.replier",
      foreignField: "_id",
      as: "replier"
    }
  },
  {
    $addFields: {
      "arrayOfReplies.replier": {
        $arrayElemAt: [
          "$replier",
          0
        ]
      }
    }
  },
  {
    $project: {
      "replier": 0
    }
  },
  {
    $group: {
      _id: "$_id",
      "arrayOfReplies": {
        "$push": "$arrayOfReplies"
      },
      commentText: {
        "$first": "$commentText"
      }
    }
  }
]);

score 1 · Answer 2 · answered Mar 15 '20 at 17:07

After your lookup stage, each document will have

{
 commentText: "text",
 arrayOfReplies: <single reply, with replier ID>
 replier: [<looked up replier data>]
}

Use an $addFields stage to move that replier data inside the reply object before the group, like:

{$addFields: {"arrayOfReplies.replier":"$replier"}}

Then your group stage will rebuild arrayOfReplies like you want.

score 0 · Accepted Answer · answered Mar 18 '20 at 11:23

All the answers provided did not solve this issue as stated in the question.

I am trying to populate the replier field inside the array arrayOfReplies. I have tried several variations of the aggregation query below. The ones that have come close to what I am trying to achieve have one short-coming. The comments that do not have replies have an arrayOfReplies array that has an empty object. I.e arrayOfReplies: [{}], essentially meaning that the array is not empty.

I wanted an aggregation that returns an empty array (not an array with an empty object) when the array is empty.

I was able to achieve what I wanted by using the code below:

 arrayOfReplies: 
     {$cond:{
         if: { $eq: ['$arrayOfReplies', {} ] },
         then: "$$REMOVE",
         else: {
                 _id : '$arrayOfReplies._id', 
                 replyText:'$arrayOfReplies.replyText',
               }
     }}

If you combine the code above with @SuleymanSah's answer you get the full working code.

How to $lookup/populate an embedded document that is inside an array?

3 Answers3