MongoDB按日期与商品汇总：将商品名称设为输出键实现问询

Got it, let's tackle this problem step by step. You want to aggregate your data to get total quantities grouped by date and item, with item names as keys in the final output—here's how to do it with MongoDB's aggregation pipeline.

First, let's recap the sample data you're working with (I'll add a couple more entries to make the example clearer):

[
  { "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") },
  { "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") },
  { "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") },
  { "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 3, "date" : ISODate("2014-01-01T10:00:00Z") },
  { "_id" : 5, "item" : "jkl", "price" : 20, "quantity" : 2, "date" : ISODate("2014-02-04T09:00:00Z") }
]

The Aggregation Pipeline Solution #

We'll use a two-stage $group along with $dateToString to standardize dates and $arrayToObject to turn item names into keys. Here's the full pipeline:

db.collection.aggregate([
  // Stage 1: Group by date (formatted to YYYY-MM-DD) and item, calculate total quantity
  {
    $group: {
      _id: {
        date: { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
        item: "$item"
      },
      totalQuantity: { $sum: "$quantity" }
    }
  },
  // Stage 2: Group by date, and restructure items into key-value pairs
  {
    $group: {
      _id: "$_id.date",
      itemTotals: {
        $push: {
          k: "$_id.item",
          v: "$totalQuantity"
        }
      }
    }
  },
  // Optional: Convert the itemTotals array into an object with item names as keys
  {
    $addFields: {
      itemTotals: { $arrayToObject: "$itemTotals" },
      date: "$_id"
    }
  },
  // Optional: Clean up the output by removing the _id field if needed
  {
    $project: {
      _id: 0
    }
  }
])

Let's Break Down Each Stage #

• First $group: We group by a composite key of formatted date (so all entries from the same day are grouped together) and item name. We use $sum to calculate the total quantity for each date-item pair.
• Second $group: Now we group by the date alone, and use $push to create an array of objects where each object has k (the item name) and v (the total quantity). This array is what we'll convert into our desired key-value structure.
• $addFields: Using $arrayToObject, we turn that itemTotals array into an object where each key is the item name, and the value is the total quantity for that date. We also add a date field for clarity.
• $project: Optional step to remove the default _id field and make the output cleaner.

Sample Output #

Running this pipeline on our sample data will give you results like this:

[
  {
    "itemTotals": { "abc": 5 },
    "date": "2014-01-01"
  },
  {
    "itemTotals": { "jkl": 1, "xyz": 5 },
    "date": "2014-02-03"
  },
  {
    "itemTotals": { "jkl": 2 },
    "date": "2014-02-04"
  }
]

If you want the item totals directly at the top level instead of nested in itemTotals, you can adjust the $addFields stage to merge the object into the root document using $mergeObjects:

{
  $addFields: {
    date: "$_id",
    ...{ $arrayToObject: "$itemTotals" }
  }
}

This would give you output like:

[
  {
    "date": "2014-01-01",
    "abc": 5
  },
  {
    "date": "2014-02-03",
    "jkl": 1,
    "xyz": 5
  },
  ...
]

That's it! This should exactly meet your requirement of counting totals by date and item, with item names as keys in the output.

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