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.

your example aggregation sample shows $lookup to a collection called 'users'. I don't see examples of these documents in your post. — barrypicker
– barrypicker, Commented Jul 4, 2021 at 3:53
You are performing 2 $lookups and using output field names that are overwriting another field. My recommendation is to always use a new field name and not pave over the existing field names. Also, the second $lookup is pulling data from the collection 'users' which may or may not have a title attribute - you have not provided any examples of documents in the users collection, but I suspect the users collection does not have a field called 'title' and thus it will never have one in the output. — barrypicker
– barrypicker, Commented Jul 4, 2021 at 4:08
Author of posts array's document can be different than blogs author. So, i want author of post to be included as posts.author. — Rahul
– Rahul, Commented Jul 4, 2021 at 5:02

turivishal · Accepted Answer · 2021-07-04 06:42:31Z

$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

Collectives™ on Stack Overflow

Lookup nested fields in mongodb array

Schema

What i want

Query

Response

Users Collection

Problem

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

Schema

What i want

Query

Response

Users Collection

Problem

1 Answer 1

Comments

Related