$查找子数组中的每个元素

所以在我的数据库中，我有3个集合，它们看起来像这样：

顾客：

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

点评：

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

评论：

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

客户可以发表评论，也可以评论其他评论。所以，我想要的是一个mongodb aggregation查询：

1. 检索评论。
2. 进行审核的客户的数据。
3. 对该审查的评论。
4. 对该评论发表评论的客户的数据。

即

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

你可以从comments集合和$lookup与customers开始获得customer名称，然后你可以$group所有评论通过审查和$lookup两次（与reviews和customer）。每次你知道这是一对一的关系，你可以在$unwind之后使用$lookup。尝试：

dbments.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
        }
    }
])

输出：

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

编辑：如果你想从reviews开始并将其过滤成单个电影，你可以使用$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
        }
    }
])

带来相同的输出

$查找子数组中的每个元素

所以在我的数据库中，我有3个集合，它们看起来像这样：

顾客：

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

点评：

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

评论：

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

客户可以发表评论，也可以评论其他评论。所以，我想要的是一个mongodb aggregation查询：

1. 检索评论。
2. 进行审核的客户的数据。
3. 对该审查的评论。
4. 对该评论发表评论的客户的数据。

即

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

你可以从comments集合和$lookup与customers开始获得customer名称，然后你可以$group所有评论通过审查和$lookup两次（与reviews和customer）。每次你知道这是一对一的关系，你可以在$unwind之后使用$lookup。尝试：

dbments.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
        }
    }
])

输出：

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

编辑：如果你想从reviews开始并将其过滤成单个电影，你可以使用$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
        }
    }
])

带来相同的输出