MongoDB-从订单中汇总销售表

发布于 2025-02-07 19:41:09 字数 995 浏览 1 评论 0原文

假设每个文档都包含一个称为产品的对象。

//文档1

{
  id: "62aac8cfb5722d4c628a4a24";
  products: [
    {
      productId: "62aac8cfb5722d4c628a4a24",
      quantity: 1,
      total: 50,
    },
  ],
}

//文档2

{
  id: "62aac8cfb5722d4c628a4a24";
  products: [
    {
      productId: "62aac8cfb5722d4c628a4a24",
      quantity: 2,
      total: 100,
    },
    {
      productId: "65fasd454daer57f2ads4c",
      quantity: 2,
      total: 100,
    },
  ],
}

//文档3

{
  id: "62aac8cfb5722d4c628a4a24";
  products: [
    {
      productId: "62aac8cfb5722d4c628a4a24",
      quantity: 5,
      total: 200,
    },
  ],
}

现在，每个文档包含出售的产品数组，例如带有ID“ 62AAC8CFB5722D4C628A4A24”的产品出现在多个订单中。我要做的是使用聚合返回称为销售的对象数组。数组中的每个对象都有产品ID（唯一），所有文档的数量总和和所有文档的总和。

[{
    productId:"62aac8cfb5722d4c628a4a24"
    quantity:8,
    total:350
},
{
    productId:"65fasd454daer57f2ads4c"
    quantity:2,
    total:200
}]

原文

Assuming there is a collection where each document contains an array of objects called products.

//Document 1

{
  id: "62aac8cfb5722d4c628a4a24";
  products: [
    {
      productId: "62aac8cfb5722d4c628a4a24",
      quantity: 1,
      total: 50,
    },
  ],
}

//Document 2

{
  id: "62aac8cfb5722d4c628a4a24";
  products: [
    {
      productId: "62aac8cfb5722d4c628a4a24",
      quantity: 2,
      total: 100,
    },
    {
      productId: "65fasd454daer57f2ads4c",
      quantity: 2,
      total: 100,
    },
  ],
}

//Document 3

{
  id: "62aac8cfb5722d4c628a4a24";
  products: [
    {
      productId: "62aac8cfb5722d4c628a4a24",
      quantity: 5,
      total: 200,
    },
  ],
}

Now each document contains an array of products sold, for example product with the id "62aac8cfb5722d4c628a4a24" appears in multiple orders. what I want to do is use aggregate to return an array of objects called sales. each object in the array has the product Id (unique), sum of quantity from all documents and sum of total from all documents.

[{
    productId:"62aac8cfb5722d4c628a4a24"
    quantity:8,
    total:350
},
{
    productId:"65fasd454daer57f2ads4c"
    quantity:2,
    total:200
}]

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

沉鱼一梦 2025-02-14 19:41:09

$ undind - 解构产品数组到多个文档。
$ group - 组成products.produtid和products.quantity和products.total的总和。
$ project - 装饰输出文档。

db.collection.aggregate([
  {
    $unwind: "$products"
  },
  {
    $group: {
      _id: "$products.productId",
      quantity: {
        $sum: "$products.quantity"
      },
      total: {
        $sum: "$products.total"
      }
    }
  },
  {
    "$project": {
      _id: 0,
      productId: "$_id",
      quantity: 1,
      total: 1
    }
  }
])

示例mongo playground

$unwind - Deconstruct products array to multiple documents.
$group - Group by products.produtId and sum for products.quantity and products.total.
$project - Decorate output documents.

db.collection.aggregate([
  {
    $unwind: "$products"
  },
  {
    $group: {
      _id: "$products.productId",
      quantity: {
        $sum: "$products.quantity"
      },
      total: {
        $sum: "$products.total"
      }
    }
  },
  {
    "$project": {
      _id: 0,
      productId: "$_id",
      quantity: 1,
      total: 1
    }
  }
])

Sample Mongo Playground

回复收藏 0 原文

~没有更多了~