MongoDB基于多日期范围查询记录并按日期分组的实现问询

Got it, let's work through this problem. You have documents with nested events arrays, and you need to fetch records while grouping those events into multiple specified date ranges. MongoDB's aggregation framework is exactly what you need here—let's break down the step-by-step implementation.

Step 1: Define Your Target Date Ranges #

First, you'll need to define the date ranges you want to group by. For example, let's say you want two ranges:

• May 11, 2017 (full day)
• May 12, 2017 (full day)

You can represent these as an array of objects with start and end ISO dates (adding a name makes group identification easier later):

const dateRanges = [
  {
    name: "May 11, 2017",
    start: ISODate("2017-05-11T00:00:00.000Z"),
    end: ISODate("2017-05-11T23:59:59.999Z")
  },
  {
    name: "May 12, 2017",
    start: ISODate("2017-05-12T00:00:00.000Z"),
    end: ISODate("2017-05-12T23:59:59.999Z")
  }
];

Step 2: Aggregation Pipeline Implementation #

Here's the full aggregation pipeline that splits, filters, groups, and restructures your data to meet your needs:

db.yourCollectionName.aggregate([
  // 1. Split the nested events array into individual documents
  { $unwind: "$events" },

  // 2. Tag each event with its matching date range
  {
    $addFields: {
      "events.date_range": {
        $switch: {
          branches: dateRanges.map(range => ({
            case: {
              $and: [
                { $gte: ["$events.date", range.start] },
                { $lte: ["$events.date", range.end] }
              ]
            },
            then: range.name
          })),
          default: "Out of specified ranges" // Mark events outside your target ranges
        }
      }
    }
  },

  // 3. Optional: Filter out events that don't fall into any target range
  {
    $match: {
      "events.date_range": { $ne: "Out of specified ranges" }
    }
  },

  // 4. Group events by original document + date range
  {
    $group: {
      _id: {
        doc_id: "$_id",
        user: "$user",
        event_group: "$event_group",
        date_range: "$events.date_range"
      },
      events_in_range: { $push: "$events" },
      total_points: { $sum: "$events.current_points" } // Optional: calculate total points per range
    }
  },

  // 5. Reassemble original documents with grouped date ranges
  {
    $group: {
      _id: "$_id.doc_id",
      user: { $first: "$_id.user" },
      event_group: { $first: "$_id.event_group" },
      date_range_groups: {
        $push: {
          range_name: "$_id.date_range",
          events: "$events_in_range",
          total_points: "$total_points" // Include if you added the sum metric
        }
      }
    }
  }
]);

How Each Step Works #

• $unwind: Takes the nested events array and creates a separate document for each event, letting us evaluate each event's date individually.
• $addFields: Uses $switch to check which date range the event falls into, tagging it with the range's name. The default case handles events outside your target ranges.
• $match: (Optional) Filters out events that aren't in your specified ranges if you don't want to include them in results.
• First $group: Groups events by the original document's identifiers plus the date range. It collects all events in the range and can calculate total points (if you need that metric).
• Second $group: Reassembles the original document structure, combining all date range groups into a single array so you have one document per original record, with neatly grouped events.

Optimization Tip #

To speed up date filtering for large datasets, create an index on the events.date field:

db.yourCollectionName.createIndex({ "events.date": 1 });

This will make the $gte/$lte checks in the $switch clause much faster.

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