如何统计MongoDB中关联productId的同category总数？

我是MongoDB新手，需求是统计Cart集合中关联productId对应的相同category的总数量，预期结果为7。最初使用populate方法获取了关联的category数据，但不知如何统计；改用aggregate结合$lookup操作时，却得到了空的product数组。

相关代码及返回结果 #

CartSchema.js #

const CartSchema = new mongoose.Schema({
    productId: {type: mongoose.Schema.Types.ObjectId, ref: 'Product'}
})

export default mongoose.model('Cart', CartSchema)

ProductSchema.js #

const ProductSchema = new mongoose.Schema({
    category: {type: String, required: true},

})

export default mongoose.model('Product', ProductSchema)

初始populate路由代码 #

router.get('/categories', async (req, res) => {
    
  try {
    const cart = await Cart.find()
    .populate([
      {path: 'productId', select: 'category' },
    ]).exec()
    res.status(200).json(cart);     

  } catch (error) { 
    res.status(500).json({error: error.message})
  }
})

populate返回结果 #

[
    {
        "_id": "63b410fdde61a124ffd95a51",
        "productId": {
            "_id": "63b410d6de61a124ffd9585b",
            "category": "CASE"
        }
    },
    {
        "_id": "63b41a679950cb7c5293bf12",
        "productId": {
            "_id": "63b41637e3957a541eb59e81",
            "category": "CASE"
        }
    },
    {
        "_id": "63b433ef226742ae6b30b991",
        "productId": {
            "_id": "63b41637e3957a541eb59e81",
            "category": "CASE"
        }
    },
    {
        "_id": "63b670dc62b0f91ee4f8fbd9",
        "productId": {
            "_id": "63b410d6de61a124ffd9585b",
            "category": "CASE"
        }
    },
    {
        "_id": "63b6710b62b0f91ee4f8fc13",
        "productId": {
            "_id": "63b410d6de61a124ffd9585b",
            "category": "CASE"
        }
    },
    {
        "_id": "63b671bc62b0f91ee4f8fc49",
        "productId": {
            "_id": "63b410d6de61a124ffd9585b",
            "category": "CASE"
        }
    },
    {
        "_id": "63b6721c62b0f91ee4f8fcc5",
        "productId": {
            "_id": "63b410d6de61a124ffd9585b",
            "category": "CASE"
        }
    }
]

改用的aggregate路由代码 #

router.get('/categories', async (req, res) => {
 try {
    const cart = await Cart.aggregate([
      {
        $lookup: {
          from: 'product',
          localField: 'productId',
          foreignField: '_id',
          as: 'product'
        }
      },
      {
        $unwind: "$product"
      },
      {
        $group: {
          _id: "$product.category",
          total: {
            $sum: 1
          }
        }
      },
      {
        $sort: {total: -1}
      },
      {
        $project: {
          _id: 0,
          category: "$_id",
          total: 1
        }
      }
    ])
    res.status(200).json(cart);     
  } catch (error) { 
    res.status(500).json({error: error.message})
  }
})

1. 修复aggregate的$lookup空数组问题 #

问题出在$lookup的from参数上：Mongoose默认会把模型名转为小写复数形式作为集合名，你的Product模型对应的集合实际是products，不是product。修改from参数即可：

router.get('/categories', async (req, res) => {
 try {
    const cart = await Cart.aggregate([
      {
        $lookup: {
          from: 'products', // 此处改为products
          localField: 'productId',
          foreignField: '_id',
          as: 'product'
        }
      },
      {
        $unwind: "$product"
      },
      {
        $group: {
          _id: "$product.category",
          total: {
            $sum: 1
          }
        }
      },
      {
        $sort: {total: -1}
      },
      {
        $project: {
          _id: 0,
          category: "$_id",
          total: 1
        }
      }
    ])
    res.status(200).json(cart);     
  } catch (error) { 
    res.status(500).json({error: error.message})
  }
})

修改后执行，就能正确关联Product集合，得到预期结果：[{"category":"CASE","total":7}]

2. 基于populate结果的统计方案 #

如果想继续用populate拿到数据后统计，可以用JavaScript的reduce方法在内存中计算：

router.get('/categories', async (req, res) => {
  try {
    const cart = await Cart.find()
      .populate([{path: 'productId', select: 'category' }])
      .exec();

    // 统计相同category的数量
    const categoryCount = cart.reduce((acc, item) => {
      const category = item.productId?.category;
      if (category) {
        acc[category] = (acc[category] || 0) + 1;
      }
      return acc;
    }, {});

    // 转换成目标格式
    const result = Object.entries(categoryCount).map(([category, total]) => ({
      category,
      total
    }));

    res.status(200).json(result);     
  } catch (error) { 
    res.status(500).json({error: error.message})
  }
})

该方案适合数据量不大的场景，数据量大时推荐用aggregate在数据库层面统计，性能更优。

内容的提问来源于stack exchange，提问作者Zurcemozz