MongoDB聚合查询求助：按日期范围过滤嵌套字段命名键

Hey there! Let's work through this MongoDB aggregation challenge together. Based on your description, you need to filter nested date-keyed values within a specific date range and restructure the output to match your desired format. Here's a step-by-step solution tailored to your document structure:

Key Approach #

Since your dates are stored as field names (not values) inside each col1, col2, col3 object, we'll need to:

• Convert each date-keyed object into an array of key-value pairs using $objectToArray
• Filter the array to keep only entries where the date falls within your target range
• Convert the filtered array back into an object with $arrayToObject
• Restructure the final output to match your desired format

Complete Aggregation Query #

Here's the full aggregation pipeline. Replace the startDate and endDate values with your desired range (formatted as ISODate):

db.yourCollectionName.aggregate([
  // Step 1: Convert each col's date-keyed object to an array
  {
    $project: {
      col1: { $objectToArray: "$data.col1" },
      col2: { $objectToArray: "$data.col2" },
      col3: { $objectToArray: "$data.col3" },
      _id: 0
    }
  },
  // Step 2: Filter each array to keep only dates in the target range
  {
    $project: {
      col1: {
        $filter: {
          input: "$col1",
          as: "item",
          cond: {
            $and: [
              {
                $gte: [
                  { $dateFromString: { dateString: "$$item.k", format: "%d-%m-%Y" } },
                  ISODate("2012-07-12T00:00:00Z") // Replace with your start date
                ]
              },
              {
                $lte: [
                  { $dateFromString: { dateString: "$$item.k", format: "%d-%m-%Y" } },
                  ISODate("2012-07-14T23:59:59Z") // Replace with your end date
                ]
              }
            ]
          }
        }
      },
      col2: {
        $filter: {
          input: "$col2",
          as: "item",
          cond: {
            $and: [
              { $gte: [ { $dateFromString: { dateString: "$$item.k", format: "%d-%m-%Y" } }, ISODate("2012-07-12T00:00:00Z") ] },
              { $lte: [ { $dateFromString: { dateString: "$$item.k", format: "%d-%m-%Y" } }, ISODate("2012-07-14T23:59:59Z") ] }
            ]
          }
        }
      },
      col3: {
        $filter: {
          input: "$col3",
          as: "item",
          cond: {
            $and: [
              { $gte: [ { $dateFromString: { dateString: "$$item.k", format: "%d-%m-%Y" } }, ISODate("2012-07-12T00:00:00Z") ] },
              { $lte: [ { $dateFromString: { dateString: "$$item.k", format: "%d-%m-%Y" } }, ISODate("2012-07-14T23:59:59Z") ] }
            ]
          }
        }
      }
    }
  },
  // Step 3: Convert filtered arrays back to date-keyed objects
  {
    $project: {
      col1: { $arrayToObject: "$col1" },
      col2: { $arrayToObject: "$col2" },
      col3: { $arrayToObject: "$col3" }
    }
  }
])

How to Pass Dynamic Dates #

If you're using a MongoDB driver (like Node.js), you can replace the hardcoded ISODate values with dynamic variables. For example:

// Define your date range (adjust as needed)
const startDate = new Date("2012-07-12");
const endDate = new Date("2012-07-14");

// Update the aggregation pipeline to use variables
db.yourCollectionName.aggregate([
  // ... (same project stages as above)
  {
    $project: {
      col1: {
        $filter: {
          input: "$col1",
          as: "item",
          cond: {
            $and: [
              { $gte: [ { $dateFromString: { dateString: "$$item.k", format: "%d-%m-%Y" } }, startDate ] },
              { $lte: [ { $dateFromString: { dateString: "$$item.k", format: "%d-%m-%Y" } }, endDate ] }
            ]
          }
        }
      },
      // ... repeat for col2 and col3
    }
  },
  // ... (final project stage)
])

Important Notes #

• Date Format: The $dateFromString operator uses the format "%d-%m-%Y" to match your DD-MM-YYYY date strings. Ensure all date keys in your documents follow this exact format to avoid parsing errors.
• Handling Invalid Dates: If you might have malformed date keys, you can add $ifNull to handle errors gracefully (e.g., { $dateFromString: { dateString: "$$item.k", format: "%d-%m-%Y", onError: null } } and exclude null values in the filter).
• Multiple Documents: This pipeline processes each document individually. If you need to aggregate results across multiple documents, you can add a $group stage at the end to combine data.

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