时间戳

首页 > 解决方案 > Mongodb在聚合查询中填充字段

mongodb - Mongodb在聚合查询中填充字段

问题描述

我有这三个集合（产品、用户和评论）

一、产品型号

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 }
});

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

      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”部分的所有列出的评论上，我需要用它填充它（需要名字，姓氏......）字段。

知道我该怎么做吗？

标签： mongodbmongoosenosqlaggregation-framework

解决方案

你所做的是正确的。在 和 之间查找之后Product，Comment您还需要进行另一个查找才能加入User。

您需要添加以下阶段以实现您的目标

  • unwind非结构化comments[]数组
  • lookup加入User收藏
  • 由于查找提供了一个数组，我们可以在$arrayElemAt安全操作符的帮助下获取数组的第一个元素$ifNull。（如果comments []为空，则脚本会抛出错误。我们使用 来处理$ifNull）
  • 我们已经对数组进行了非结构化，group有助于重新组合它

阶段如下

  {
    $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"
      }
    }
  }

工作Mongo游乐场

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

推荐阅读