MongoDB中如何基于数组内最新嵌入对象筛选用户？

Problem Overview #

You're building a page filtering system for your User collection, needing to filter users that meet three specific criteria:

• firstName, lastName, or email contains 'john' (case-insensitive)
• Their latest subscription (the one with the most recent startDate) has a status of EXPIRED
• Their latest subscription plan is TRIAL

Your current find() query doesn't work because it matches any subscription in the array (not the latest one), and you can't use virtual fields for database-level queries. The expected result for your sample user is null, since their latest subscription is a cancelled BASIC plan.

Solution: Use MongoDB Aggregation Framework #

The aggregation framework lets us manipulate the subscription array to isolate the latest entry, then filter based on that. Here's how to do it:

User.aggregate([
  // Step 1: Match users where name/email contains 'john' (case-insensitive)
  {
    $match: {
      $or: [
        { firstName: { $regex: 'john', $options: 'i' } },
        { lastName: { $regex: 'john', $options: 'i' } },
        { email: { $regex: 'john', $options: 'i' } }
      ]
    }
  },
  // Step 2: Add a field containing only the latest subscription
  {
    $addFields: {
      latestSubscription: {
        // Sort subscriptions by startDate descending, then take the first element
        $first: {
          $sortArray: {
            input: '$subscriptions',
            sortBy: { startDate: -1 }
          }
        }
      }
    }
  },
  // Step 3: Filter users where latest subscription meets the criteria
  {
    $match: {
      'latestSubscription.status': 'EXPIRED',
      'latestSubscription.plan': 'TRIAL'
    }
  },
  // Step 4: Handle pagination (skip/limit)
  { $skip: skip },
  { $limit: limit }
])

Breakdown of Each Step #

• Initial $match: Narrow down the user pool to only those with 'john' in their name/email—this reduces the number of documents we need to process in later stages.
• $addFields with $sortArray + $first: We sort the subscriptions array by startDate in descending order (so the newest entry is first), then grab the first element as latestSubscription. This gives us a single, top-level field to filter against.
• Second $match: Now we can filter directly on the latestSubscription field, ensuring we only keep users where their newest subscription is both EXPIRED and a TRIAL plan.
• Pagination: Apply $skip and $limit at the end to handle your page-based filtering.

Why Your Original Query Failed #

Your find() query uses $and with 'subscriptions.status': 'EXPIRED' and 'subscriptions.plan': 'TRIAL'—but MongoDB interprets this as "the array has at least one subscription with status EXPIRED and at least one subscription with plan TRIAL" (they don't have to be the same subscription). The aggregation fixes this by isolating the latest subscription first, so we're filtering against a single, specific entry.

Why Virtual Fields Can't Be Used #

MongoDB doesn't support querying on virtual fields because virtual fields are computed client-side when documents are retrieved, not stored in the database. The aggregation framework is the right approach here because it lets us compute the "latest subscription" at the database level before filtering.

For your sample user, this aggregation will return no results (as expected), since their latest subscription is a BASIC plan with status CANCELLED.

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