Mongoose nested lookup with 3 child levels included in arrays-CodePudding

I am new to MongoDB and mongoose.

I am trying to retrieve the entire array of object hierarchy from the database as a JSON.

By searching, I learned how to group nested lookup with 3 child levels without array, but the problem is I cannot find a way to deal with the nested arrays.

Here is what I am struggling with.

user:

[
 {
  _id: ObjectId("a1"),
  username: "user1",
  name: "name1"
 },
 {
  _id: ObjectId("a2"),
  username: "user2",
  name: "name2"
 },
{
  _id: ObjectId("a3"),
  username: "user2",
  name: "name2"
 },
 ...
]

reply:

[
 {
  _id: ObjectId("a"),
  author: {ObjectId("a3")},
  title: {"Reply1"};
  body: {"Hello World!"}
 },
 {
  _id: ObjectId("b"),
  author: {ObjectId("a1")},
  title: {"Reply2"};
  body: {"Hello World!"}
 },
 {
  _id: ObjectId("c"),
  author: {ObjectId("a2")},
  title: {"Reply3"};
  body: {"Hello World!"}
 },
 {
  _id: ObjectId("d"),
  author: {ObjectId("a2")},
  title: {"Reply4"};
  body: {"Hello World!"}
 }
...
]

post:

[
//First post
 {
  _id: ObjectId("0"),
  title: 'post title',
  author: {ObjectId("a1")},
  reply: [{ObjectId("a")}, {ObjectId("b")}, ...],
 },
//Second Post
 {
  _id: ObjectId("1"),
  title: 'post title2',
  author: {ObjectId("a2")},
  reply: [{ObjectId("c")}, {ObjectId("d")}, ...],
 },
 ...
]

Expected example:

[
//First post
 {
  _id: ObjectId("0"),
  title: 'post title',
  author: {
   _id: ObjectId("a1"),
   username: "user1",
   name: "name1"
  },
  reply: [{
   _id: ObjectId("a"),
   author: {
    _id: ObjectId("a3"),
    username: "user2",
    name: "name2"
   },
   title: {"Reply1"},
   body: {"Hello World!"}
   }, 
   {
   _id: ObjectId("b"),
   author: {
    _id: ObjectId("a1"),
    username: "user1",
    name: "name1"
   },
   title: {"Reply2"};
   body: {"Hello World!"}
   },
   ...
  ],
 },
 {
 //Second post
 },
 ...
]

Here is the code that I used.

posts = await Post.aggregate([{
    $match: searchQuery
  },
  {
    $lookup: {
      from: 'users',
      localField: 'author',
      foreignField: '_id',
      as: 'author'
    }
  },

  {
    $lookup: {
      from: 'replies',
      localField: 'reply',
      foreignField: '_id',
      as: 'reply'
    }
  },
  {
    $unwind: '$author',
  },
  {
    $lookup: {
      from: 'users',
      localField: 'reply.author',
      foreignField: '_id',
      as: 'reply.author'
    }
  },
  {
    $project: {
      title: 1,
      author: 1,
      reply: 1
    }
  },
]).exec();

How should I change the code if I want to get the expected example? Thank you.

CodePudding user response：

Here's one way to do it. Lots of "$lookup" for author of post, reply to the post, author ids of reply, and then author document matching _id.

db.post.aggregate([
  {
    "$lookup": {
      "from": "user",
      "localField": "author",
      "foreignField": "_id",
      "as": "author"
    }
  },
  {
    "$set": {
      "author": {
        "$first": "$author"
      }
    }
  },
  {
    "$lookup": {
      "from": "reply",
      "localField": "reply",
      "foreignField": "_id",
      "as": "reply"
    }
  },
  {
    "$lookup": {
      "from": "user",
      "localField": "reply.author",
      "foreignField": "_id",
      "as": "replyAuthors"
    }
  },
  {
    "$project": {
      "title": 1,
      "author": 1,
      "reply": {
        "$map": {
          "input": "$reply",
          "as": "repObj",
          "in": {
            "$mergeObjects": [
              "$$repObj",
              {
                "author": {
                  "$first": {
                    "$filter": {
                      "input": "$replyAuthors",
                      "as": "repA",
                      "cond": {
                        "$eq": [
                          "$$repA._id",
                          "$$repObj.author"
                        ]
                      }
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Try it on mongoplayground.net.