Lookup nested fields in mongodb array

Question

Schema

[
    {
        "_id" : ObjectId("60e09c90402cbd625d7a8162"),
        "title" : "...",
        "author" : DBRef("users", ObjectId("60ce9146f41866120ee70c0d")),
        "posts" : [ ]
    }
    {
        "_id" : ObjectId("60e09c97402cbd625d7a8163"),
        "title" : "...",
        "author" : DBRef("users", ObjectId("60ce9146f41866120ee70c0d")),
        "posts" : [ ]
    }
    {
        "_id" : ObjectId("60e09d06402cbd625d7a8164"),
        "title" : "...",
        "author" : DBRef("users", ObjectId("60ce9146f41866120ee70c0d")),
        "posts" : [ ]
    }
    {
        "_id" : ObjectId("60e09d07402cbd625d7a8165"),
        "title" : "...",
        "author" : DBRef("users", ObjectId("60ce9146f41866120ee70c0d")),
        "posts" : [
            {
                "_id" : ObjectId("60e12300e931cd14c03ecd89"),
                "title" : "...",
                "author" : DBRef("users", ObjectId("60ce9146f41866120ee70c0d"))
            },
            {
                "_id" : ObjectId("60e12305e931cd14c03ecd8a"),
                "title" : "...",
                "author" : DBRef("users", ObjectId("60ce9146f41866120ee70c0d"))
            }
        ]
    }
]

Each document will have a title, author and posts attribute. author attribute is a $ref and posts attribute is an array of documents. Each document of posts will also have title, author.

What i want

I want to get all documents with authors and it's posts(with author).

Query

db.blogs.aggregate([
  { $project: {title:1,author:1,"posts.title":1,"posts._id":1,"posts.author":1} },
  { "$lookup": { "from": "users", "localField": "author.$id", "foreignField": "_id", "as": "author" } },
  { "$lookup": { "from": "users", "localField": "posts.author.$id", "foreignField": "_id", "as": "posts.author" } }
])

Response

[
  {
    "_id": '..',
    "title": "..",
    "author": [
      {
        // author details
      }
    ],
    "posts": {
      "author": []
    }
  },
  {
    "_id": '..',
    "title": "...",
    "author": [
      {
        // author details
      }
    ],
    "posts": {
      "author": []
    }
  },
  {
    "_id": '..',
    "title": "..",
    "author": [
      {
        // author details
      }
    ],
    "posts": {
      "author": []
    }
  },
  {
    "_id": ...,
    "title": "2 Type SR Blog, my first blog.",
    "author": [
      {
        // author details
      }
    ],
    "posts": {
      "author": [
        {
        // author details
        }
      ]
    }
  }
]

Users Collection

{
    "_id" : ObjectId("60ce9146f41866120ee70c0d"),
    "name" : "Rahul kumar",
    "status" : "A Fake Developer",
    "__v" : 0,
    "pic" : "https://res.cloudinary.com/bdevg/image/upload/v1604287418/pic_xexz8o.jpg"
}

Problem

You can see the last document, which only has posts attribute. It should also contain title attribute.

Answer 1

• $unwind to deconstruct the posts array
• $lookup with users collection and pass posts.author as localField
• $unwind to deconstruct author array
• $group by _id and reconstruct the posts array and get first value for other required fields
• $lookup with users and pass author as localField
• $unwind to deconstruct the author array

db.blogs.aggregate([
  {
    $unwind: {
      path: "$posts.author",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "posts.author",
      "foreignField": "_id",
      "as": "posts.author"
    }
  },
  {
    $unwind: {
      path: "$posts.author",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: "$_id",
      title: { $first: "$title" },
      posts: { $push: "$posts" },
      author: { $first: "$author" }
    }
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "author",
      "foreignField": "_id",
      "as": "author"
    }
  },
  {
    $unwind: {
      path: "$author",
      preserveNullAndEmptyArrays: true
    }
  }
])

Playground