$lookup for each element within a sub-array

Question

So in my Database, I have the 3 collections, and they look like this:

Customers:

customers = [
  {_id: 1, username: "jack", ... },
  {_id: 2, username: "jane", ... }
  ...
]

Reviews:

reviews = [
  { _id: 1, customerID: 1, message: "my message", ...}
  ...
]

Comments:

comments = [
  { _id: 1, reviewID: 1, customerID: 2, message: "my response" ...}
  ...
]

Customers can post reviews, and can also comment on other reviews. So, what I want is a mongodb aggregation query to:

1. Retrieve the reviews.

2. The data of the customer who made the review.

3. The comments on that review.

4. The data of the customers who made the comment on that review.

i.e

reviews = [
  {
    _id: 1,
    username: "jack",
    message: "my message"
    comments: [
      { _id: 1, username: "jane", message: "my response", ...},
      ...
    ]
    ...
  }
  ...
]

Answer 1

You can start from comments collection and $lookup with customers to get customer name, then you can $group all comments by review and $lookup twice (with reviews and customer). Every time you know that it's a one-to-one relationship you can use $unwind after $lookup. Try:

db.comments.aggregate([
    {
        $lookup: {
            from: "customers",
            localField: "customerID",
            foreignField: "_id",
            as: "customer"
        }
    },
    {
        $unwind: "$customer"
    },
    {
        $project: {
            _id: 1,
            reviewID: 1,
            username: "$customer.username",
            message: 1
        }
    },
    {
        $group: {
            _id: "$reviewID",
            comments: { $push: { _id: "$_id", username: "$username", message: "$message" } }
        }
    },
    {
        $lookup: {
            from: "reviews",
            localField: "_id",
            foreignField: "_id",
            as: "review"
        }
    },
    {
        $unwind: "$review"
    },
    {
        $lookup: {
            from: "customers",
            localField: "review.customerID",
            foreignField: "_id",
            as: "customer"
        }
    },
    {
        $unwind: "$customer"
    },
    {
        $project: {
            _id: 1,
            message: "$review.message",
            username: "$customer.username",
            comments: 1
        }
    }
])

Outputs:

{ "_id" : 1, "comments" : [ { "_id" : 1, "username" : "jane", "message" : "my response" } ], "message" : "my message", "username" : "jack" }

EDIT: If you want to start from reviews and filter it out to single movie you can then you can use $lookup with custom pipeline

db.reviews.aggregate([
    {
        $match: {
            movieId: 1,
        }
    },
    {
        $lookup: {
            from: "customers",
            localField: "customerID",
            foreignField: "_id",
            as: "customer"
        }
    },
    {
        $unwind: "$customer"
    },
    {
        $lookup: {
            from: "comments",
            let: { reviewId: "$_id" },
            pipeline: [
                {
                    $match: { $expr: { $eq: [ "$$reviewId", "$reviewID" ] } }
                },
                {
                    $lookup: {
                        from: "customers",
                        localField: "customerID",
                        foreignField: "_id",
                        as: "customer"
                    }
                },
                {
                    $unwind: "$customer"
                },
                {
                    $project: {
                        _id: 1,
                        message: 1,
                        username: "$customer.username"
                    }
                }
            ],
            as: "comments"
        }
    },
    {
        $project: {
            _id: 1,
            message: 1,
            username: "$customer.username",
            comments: 1
        }
    }
])

Brings the same output

Answer 2

You can use below aggregation with mongodb 3.6 and above

Reviews.aggregate([
  { "$lookup": {
    "from": Customers.collection.name,
    "let": { "customerID": "$customerID" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_id", "$$customerID"] } } }
    ],
    "as": "customer"
  }},
  { "$unwind": "$customer" },
  { "$lookup": {
    "from": Comments.collection.name,
    "let": { "reviewID": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$reviewID", "$$reviewID"] } } },
      { "$lookup": {
        "from": Customers.collection.name,
        "let": { "customerID": "$customerID" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": ["$_id", "$$customerID"] } } }
        ],
        "as": "customer"
      }},
      { "$unwind": "$customer" },
    ],
    "as": "comments"
  }}
])