时间戳

首页 > 解决方案 > MongoDB 按产品的子类别聚合和分组

mongodb - MongoDB 按产品的子类别聚合和分组

问题描述

我有一个看起来像这样的 MongoDB 架构

const ProductModel = new Schema({
subcategory: {
    type : mongoose.Schema.Types.ObjectId,
    ref : "Subcategory",
},
product_name: {
    type: String
},
description: {
    type: String
},
price: {
    type: Number
},
});

和一个子类别架构：

const SubcategoryModel = new Schema({
    subcategoryName: {
        type: String,
    }
});

聚合之前的输入查询如下所示：

[
    {
        "_id": "111",
        "subcategory": {
            "_id": "456",
            "categoryName": "Sneakers",
        },
        "product_name": "Modern sneaker",
        "description": "Stylish",
        "price": 4400
    },
    {
        "_id": "222",
        "subcategory": {
            "_id": "456",
            "categoryName": "Sneakers",
        },
        "product_name": "Blue shoes",
        "description": "Vived colors",
        "price": 7500
    },
    {
        "_id": "333",
        "subcategory": {
            "_id": "123",
            "categoryName": "Jackets",
            "__v": 0
        },
        "product_name": "Modern jacket",
        "description": "Stylish",
        "price": 4400
    },
    }
]

查询的最终结果应如下所示：

{
   "Sneakers":[
      {
         "product_name":"Modern sneaker",
         "description":"Stylish",
         "price":"4400"
      },
      {
         "product_name":"Blue shoes",
         "description":"Vived colors",
         "price":"7500"
      },
      "Jackets":{
         "...."
      }
   ]
}

聚合前的子类：

"subcategories": [
  {
    "_id": "123",
    "categoryName": "Jackets",
    
  },
  {
    "_id": "456",
    "categoryName": "Sneakers",
    
  }
]

我正在尝试填充子类别，然后按产品的 subcategoryName 字段对产品进行分组。

标签： mongodbmongoosemongodb-queryaggregation-framework

解决方案

您可以使用此聚合查询：

  • 首先$lookup做之间的连接Product和Subcategory创建数组subcategories。
  • 然后使用 解构数组$unwind。
  • $group通过子产品的名称使用 . 添加整个对象$$ROOT。
  • 传递您想要使用的字段$project。
  • 并将replaceRoot键值作为Sneakers和放入数组中Jackets。
db.Product.aggregate([
  {
    "$lookup": {
      "from": "Subcategory",
      "localField": "subcategory.categoryName",
      "foreignField": "categoryName",
      "as": "subcategories"
    }
  },
  {
    "$unwind": "$subcategories"
  },
  {
    "$group": {
      "_id": "$subcategories.categoryName",
      "data": {
        "$push": "$$ROOT"
      }
    }
  },
  {
    "$project": {
      "data": {
        "product_name": 1,
        "description": 1,
        "price": 1
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$arrayToObject": [
          [
            {
              "k": "$_id",
              "v": "$data"
            }
          ]
        ]
      }
    }
  }
])

这里的例子

使用您提供的数据，结果是：

[
  {
    "Sneakers": [
      {
        "description": "Stylish",
        "price": 4400,
        "product_name": "Modern sneaker"
      },
      {
        "description": "Vived colors",
        "price": 7500,
        "product_name": "Blue shoes"
      }
    ]
  },
  {
    "Jackets": [
      {
        "description": "Stylish",
        "price": 4400,
        "product_name": "Modern jacket"
      }
    ]
  }
]

推荐阅读