mongodb - MongoDB 对数组中的元素进行分组

Question

对于每个product_id，我想要所有订单中的价格总和以及我想要实现的另一个结果是同一数组中具有相同id的产品的价格总和。

\\orders collection:
{
order_id: 123,
customer_id: 1,
order_items: [{product_id:1, price: 2}, {product: 4, price: 8}, {product_id:1, price: 2}]
},
{
order_id: 124,
customer_id: 5,
order_items: [{product_id:5, price: 7}, {product: 4, price: 8}]
}

我想要的第一个结果：

{product_id: 1, tot_price: 4}, 
{product_id: 4, tot_price: 16},
{product_id: 5, tot_price: 7}

第二个结果：

{order_id:123,
product_tot: [{product_id:1, tot_price: 4}, {product: 4, tot_price: 8}]},
{order_id:124,
product_tot: [{product_id:5, tot_price: 7}, {product: 4, tot_price: 8}},

标签： mongodbgroup-byembedded-database

Answer 1

尝试以下查询：

第一个结果：

db.collection.aggregate([
  {
    $unwind: {
      path: "$order_items",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: "$order_items.product_id",
      tot_price: {
        $sum: "$order_items.price"
      }
    }
  },
  {
    $project: {
      _id: 0,
      product_id: "$_id",
      tot_price: 1
    }
  }
])

MongoPlayGroundLink

对于第二个结果：

db.collection.aggregate([
  {
    $unwind: {
      path: "$order_items",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: {
        order_id: "$order_id",
        product_id: "$order_items.product_id"
      },
      tot_price: {
        $sum: "$order_items.price"
      }
    }
  },
  {
    $project: {
      _id: 0,
      order_id: "$_id.order_id",
      product_id: "$_id.product_id",
      tot_price: "$tot_price"
    }
  },
  {
    $group: {
      _id: "$order_id",
      product_tot: {
        $push: {
          product_id: "$product_id",
          tot_price: "$tot_price"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      order_id: "$_id",
      product_tot: 1
    }
  }
])

MongoPlayGroundLink