MongoDB中按指定用户、时间范围及小时区间筛选并获取productIds数组最大长度的实现方案

Hey there! Let's work through this MongoDB query step by step to get the exact {count:3} result you're after.

需求梳理 #

First, let's recap the criteria we need to meet:

• Filter documents where personIds is either 13355 or 10347
• The at date falls between March 28, 2022 and March 30, 2022 (inclusive)
• The hour of the at timestamp is between 10 AM and 10 PM (10 to 22)
• From the matching docs, find the one with the longest productIds array, then output its length as {count:3}

MongoDB Aggregation Solution #

Here's the aggregation pipeline that will do exactly this (replace yourCollectionName with your actual collection name):

db.yourCollectionName.aggregate([
  // Stage 1: Filter documents that meet all our criteria
  {
    $match: {
      personIds: { $in: [13355, 10347] },
      at: {
        $gte: ISODate("2022-03-28T00:00:00.000Z"),
        $lt: ISODate("2022-03-31T00:00:00.000Z")
      },
      $expr: {
        $and: [
          { $gte: [{ $hour: "$at" }, 10] },
          { $lte: [{ $hour: "$at" }, 22] }
        ]
      }
    }
  },
  // Stage 2: Calculate the length of the productIds array for each doc
  {
    $addFields: {
      productCount: { $size: "$productIds" }
    }
  },
  // Stage 3: Sort by array length descending, then pick the first (largest) result
  {
    $sort: { productCount: -1 }
  },
  {
    $limit: 1
  },
  // Stage 4: Format the output to match the desired {count:3} structure
  {
    $project: {
      _id: 0,
      count: "$productCount"
    }
  }
])

How this works: #

• $match: We use $in to target the two personIds values. For the date range, $gte (greater than or equal to March 28) and $lt (less than March 31) ensures we cover all of March 30. The $expr lets us use aggregation operators to extract the hour from at and check it's between 10 and 22.
• $addFields: The $size operator gives us the length of the productIds array, stored in a new productCount field.
• $sort + $limit: Sorting by productCount in descending order puts the longest array first, then we limit to just that top result.
• $project: We drop the _id field (since we don't need it) and rename productCount to count to match your desired output format.

When you run this, it will return exactly {count:3} since the matching document with personIds:13355 on March 28 has 3 items in productIds—the longest among all qualifying docs.

内容的提问来源于stack exchange，提问作者Mehmet Serkan Ekinci