聚合查询上的Mongodb填充字段 [英] Mongodb populating field on aggregation query

问题描述

我有这三个集合(产品，用户和评论)

I have these three collections (Products, Users & Comments)

1.产品型号

var ProductSchema = new Schema({
  title:          { type: String, text: true, required: true },
  description:    { type: String, required: true },
  category:       [{ type: String, required: true }],
  type:           [{ type: String, required: true }],
  isUsed:         { type: Boolean, },
  manufacturer:   { type: String },
  price:          { type: Number },
});

2.用户模型

var UserSchema = new Schema({
  firstName:      { type: String, text: true, required: true },
  lastName:       { type: String, text: true, required: true },
  country:        [{ type: String, required: true }],
  phoneNumber:    [{ type: String, required: true }]
});

3.评论模型

var CommentSchema = new Schema({
  comment:        { type: String, text: true, required: true },
  commented_by:   { type: Schema.Types.ObjectId, ref: 'User', required: true },
  commented_on:   { type: Schema.Types.ObjectId, ref: 'Product', required: true }
});

然后，当发送请求以获取特定的产品时，这是在后台运行的猫鼬代码.

Then, when a request is sent to get a specific product, this is the mongoose code that runs in the background.

      var aggregate = Product.aggregate([

        { "$match": { _id: ObjectId(req.params.id) } },  /** which is the product id **/
        { "$limit": 1 },
   
        { 
          $lookup: {
              from: 'comments',
              let: { id: "$_id" },
              pipeline: [
                { $match: { $expr: { $eq: [ "$commented_on.product", "$$id" ] } } },
            ],
              as: "comments"
            }
        },  
       
        { 
          $addFields: {
            comments: "$comments",
            comments_no: { $size: "$comments" },
            hasCommented: { $in: [ObjectId(req.user._id), "$comments.commented_by" ] }, /** req.user._id  is the user's id **/
          }
        },
          {
            $project: {
              _id: 1,
              title: 1,
              description: 1,
              category: 1,
              type: 1,
              price: 1,
              comments: 1,
              hasCommented: 1,
              comments_no: 1,
            }
          }
      ]);

输出

{
    "_id": "5f992b5338f5f035f35911c5",
    "title": "Some title here..."
    "description": Some description here ...
    "price": 1000,
    "category": "Clothing",
    "type": "shoe",
    "comments": [ 
        {
          "comment": "Nice Product",
          "commented_by": "5f992b5338f5f035f35911b2",
          "commented_on": "5f992b5338f5f035f35911c5"
        },
        {
          "comment": "I like this product",
          "commented_by": "5f992b5338f5f035f35911a2",
          "commented_on": "5f992b5338f5f035f35911c5"
        },
        {
          "comment": "Great, I like it!",
          "commented_by": "5f992b5338f5f035f35911c8",
          "commented_on": "5f992b5338f5f035f35911c5"
        } 
                 
    ],
    "hasCommented": true,
    "comments_no": 3
}

预期结果

{
    "_id": "5f992b5338f5f035f35911c5",
    "title": "Some title here..."
    "description": Some description here ...
    "price": 1000,
    "category": "Clothing",
    "type": "shoe",
    "comments": [ 
        {
          "comment": "Nice Product",
          "commented_by": { 
              "_id": "5f992b5338f5f035f35911b2",
              "firstName": "Jack",
              "lastName": "Sparrow" 
          },
          "commented_on": "5f992b5338f5f035f35911c5"
        },
        {
          "comment": "I like this product",
          "commented_by": { 
              "_id": "5f992b5338f5f035f35911a2",
              "firstName": "John",
              "lastName": "Doe" 
          },
          "commented_on": "5f992b5338f5f035f35911c5"
        },
        {
          "comment": "Great, I like it!",
          "commented_by": { 
              "_id": "5f992b5338f5f035f35911c8",
              "firstName": "Mary",
              "lastName": "Jane" 
          },
          "commented_on": "5f992b5338f5f035f35911c5"
        } 
                 
    ],
    "hasCommented": true,
    "comments_no": 3
}

获取特定产品时，我现在可以列出该产品的评论，但问题出在"commented_by"上所有列出的评论上部分，我需要用它来填充它(需要firstName，lastName ...)字段.

When getting a specific product, I am able now to list comments of the product with it but the problem is on all listed comments on "commented_by" section I need to populate it (need the firstName, lastName...) field with it.

任何想法我该怎么做?

推荐答案

您所做的正确.在 Product 和 Comment 之间进行查找之后，您还需要再次查找才能加入 User .

What you have done is correct. After the lookup between Product and Comment, you need to have another lookup to join User also.

您需要添加以下步骤来实现目标

You need to add following stages to achieve your target

• unwind 取消结构化 comments [] 数组
• lookup 加入 User 集合
• 由于查找提供了一个数组，因此我们可以在安全操作符 $ ifNull 的帮助下使用 $ arrayElemAt 来获取数组的第一个元素.(如果 comments [] 为空，则脚本将引发错误.要处理此问题，我们使用 $ ifNull )
• 我们已经取消了数组的结构， group 帮助重新组合了数组

• unwind to unstructured the comments[] array
• lookup to join User collection
• Since lookup provides an array, we can get the first element of the array using $arrayElemAt with the help of safety operator $ifNull. (If the comments [] is empty, then the script throws error. To handle that we use $ifNull)
• We already unstructured the array, group helps to regroup it

阶段在下面给出

  {
    $unwind: "$comments"
  },
  {
    $lookup: {
      from: "User",
      let: {
        id: "$comments.commented_by"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$_id",
                "$$id"
              ]
            }
          }
        },
        
      ],
      as: "comments.commented_by"
    }
  },
  {
    $addFields: {
      "comments.commented_by": {
        $ifNull: [
          {
            $arrayElemAt: [
              "$comments.commented_by",
              0
            ]
          },
          {}
        ]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      title: {
        $first: "$title"
      },
      hasCommented: {
        $first: "$hasCommented"
      },
      comments: {
        $push: "$comments"
      }
    }
  }

工作中蒙戈游乐场

Working Mongo playground

注意:仅供参考，变量和集合名称可能不同.

Note : FYI,The variable and collection name may be different.

这篇关于聚合查询上的Mongodb填充字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！