MongoDB数组双字段对比查询：基于指定集合数据的技术请求

Hey there! Let's figure out how to run that array field dual comparison query on your MongoDB collection. Based on the sample data you shared, I'll cover common scenarios and actionable solutions below.

Let's say you want to find documents where at least one element in the packages array meets a dual-field condition (e.g., paid is less than totalAmount, or expiryDate is past while isActive is still true).

Method 1: Use find() with $expr (MongoDB 3.6+) #

This approach works well for simple existence checks. For example, to find users who have unpaid packages (where paid < totalAmount):

db.yourCollectionName.find({
  "$expr": {
    "$gt": [
      // Filter the packages array to get elements matching the condition
      { "$size": {
          "$filter": {
            "input": "$packages",
            "cond": { "$lt": ["$$this.paid", "$$this.totalAmount"] }
          }
        }
      },
      0 // Check if there's at least one matching element
    ]
  }
})

Customize the Condition #

If your need is date-based (e.g., find active packages that have expired), adjust the cond block:

db.yourCollectionName.find({
  "$expr": {
    "$gt": [
      { "$size": {
          "$filter": {
            "input": "$packages",
            "cond": {
              "$and": [
                { "$eq": ["$$this.isActive", true] },
                { "$lt": ["$$this.expiryDate", new Date()] }
              ]
            }
          }
        }
      },
      0
    ]
  }
})

Method 2: Use Aggregation Pipeline (For Complex Workflows) #

If you need to further process the matching array elements (e.g., return only the relevant packages instead of the whole document), use the aggregation pipeline:

db.yourCollectionName.aggregate([
  // Step 1: Match documents with at least one qualifying package
  {
    "$match": {
      "$expr": {
        "$gt": [
          { "$size": {
              "$filter": {
                "input": "$packages",
                "cond": { "$lt": ["$$this.paid", "$$this.totalAmount"] }
              }
            }
          },
          0
        ]
      }
    }
  },
  // Step 2: Optional - Extract only the matching packages into a new field
  {
    "$addFields": {
      "unpaidPackages": {
        "$filter": {
          "input": "$packages",
          "cond": { "$lt": ["$$this.paid", "$$this.totalAmount"] }
        }
      }
    }
  },
  // Optional: Project to remove unwanted fields
  {
    "$project": {
      "name": 1,
      "mobile": 1,
      "unpaidPackages": 1
    }
  }
])

• Replace yourCollectionName with your actual collection name.
• Modify the $cond logic to match your specific dual-field comparison (use $eq, $gt, $lte, etc., as needed).
• The $filter operator lets you target exactly the array elements that meet your criteria, and $size checks if any such elements exist.

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