求助：MongoDB聚合操作实现订单数据结构重组

Got it, let's work through this problem step by step. The core goal here is to group multiple documents belonging to the same order_id into a single document, while restructuring product-related fields into a dedicated section. Since one order maps to multiple items (per your original data structure), I’ll assume you want an array of products under the final Order object—if you actually need just a single product entry, I’ll note how to adjust that too.

First, let’s confirm a key assumption: all documents with the same order_id share identical date and time values (this makes logical sense for a single order). If that’s not the case, you’ll need to decide how to resolve discrepancies (e.g., take the first/last occurrence, or validate data consistency first).

Here’s the aggregation pipeline that achieves your desired structure:

db.yourCollectionName.aggregate([
  // Step 1: Group documents by order_id and collect necessary data
  {
    $group: {
      _id: "$Order.order_id",
      productList: {
        $push: {
          SKU: "$Order.SKU",
          product_name: "$Order.product_name",
          number_of_goodies: "$Order.number_of_goodies"
        }
      },
      orderDate: { $first: "$Order.date" },
      orderTime: { $first: "$Order.time" }
    }
  },
  // Step 2: Restructure output to match your target format
  {
    $project: {
      _id: 0,
      Order: {
        order_id: "$_id",
        product: "$productList", // Use this for an array of products
        // If you ONLY need one product entry, replace the line above with:
        // product: { $first: "$productList" },
        date: "$orderDate",
        time: "$orderTime"
      }
    }
  }
])

Breakdown of each stage: #

1. $group Stage:

   • We group all documents by Order.order_id (using _id as the grouping key).
   • $push collects each product’s details into an array called productList—this preserves all items from the same order.
   • $first grabs the date and time from the first document in the group. Since all order documents should share these values, $last would work equally well.

2. $project Stage:

   • We exclude the auto-generated _id field with _id: 0.
   • We build the final Order object:
     • Map the grouping key (_id) to order_id.
     • Assign the collected productList array to the product field. If you only need a single product entry (e.g., the first item in the group), swap $productList with { $first: "$productList" }.
     • Attach the shared date and time values.

Quick adjustment if you need a single product object: #

If your target structure truly requires a single product object instead of an array (maybe you have duplicate documents for the same order/item), just use { $first: "$productList" } in the $project stage to pick the first product entry from the group.

Don’t forget to replace yourCollectionName with the actual name of your MongoDB collection!

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