MongoDB按rentalContractId与价格范围过滤productPrice数组对象求助

我有一个products集合，其中productPrice字段是包含两个对象的数组。在产品列表场景中，我需要根据ObjectId类型的rentalContractId以及指定的价格范围（最大值到最小值），仅返回productPrice中满足rentalContractId匹配且价格在范围内的产品。

我尝试用以下查询片段实现，但遇到了问题：

db.products.aggregate([......, {
        "$match": {
            "productPrice": {
                "$elemMatch": {
                    "rentalContractId": ObjectId("64018dfc8a7d37f378d6c426"),
                    "price": {
                        "$lte": 9,
                        "$gte": 4           
                    }
                }
            },
        }
    }, .......]);

以下是完整的聚合查询代码：

db.products.aggregate([
    {
        "$match": {
            "$and": [
                {
                    "isDeleted": false
                },
                {
                    "isArchived": false
                },
                {
                    "isProductDisplayAble": true
                },
                {
                    "createdBy": {
                        "$ne": "6469a515159d16142e64b68e"
                    }
                },
                {
                    "bookings": {
                        "$not": {
                            "$elemMatch": {
                                "productBookingCompleteDate": {
                                    "$gte": "2023-05-21T04:59:01.105Z"
                                },
                                "productBookingStartDate": {
                                    "$lte": "2023-05-21T04:59:01.105Z"
                                }
                            }
                        }
                    }
                }
            ]
        }
    },
    {
        "$match": {
            "productTags": {
                "$in": [
                    "6456c1eb883d8e08841911ea"
                ]
            }
        }
    },
    // here
    {
    $addFields: {
      productPrice: {
        $filter: {
          input: "$productPrice",
          cond: { $and: [ { $eq: [ "$$this.rentalContractId", ObjectId("64018dfc8a7d37f378d6c426") ] }, { $gte: [ "$$this.price", 4 ] }, { $lte: [ "$$this.price", 9 ] } ] }
        }
      }
    }
    },
    {
        "$lookup": {
            "from": "rentalcontractdetails",
            "localField": "productPrice.rentalContractId",
            "foreignField": "_id",
            "as": "rentalContract",
            "pipeline": [
                {
                    "$project": {
                        "contractType": 1
                    }
                }
            ]
        }
    },
    {
        "$lookup": {
            "from": "brands",
            "localField": "brandId",
            "foreignField": "_id",
            "as": "brandId"
        }
    },
    {
        "$lookup": {
            "from": "dimensions",
            "localField": "productTags",
            "foreignField": "_id",
            "as": "dimensions"
        }
    },
    {
        "$lookup": {
            "from": "ratings",
            "localField": "_id",
            "foreignField": "productId",
            "as": "ratings"
        }
    },
    {
        "$lookup": {
            "from": "categoryones",
            "localField": "categoryOneId",
            "foreignField": "_id",
            "as": "categoryOneId"
        }
    },
    {
        "$lookup": {
            "from": "categorytwos",
            "localField": "categoryTwoId",
            "foreignField": "_id",
            "as": "categoryTwoId"
        }
    },
    {
        "$lookup": {
            "from": "categorythrees",
            "localField": "categoryThreeId",
            "foreignField": "_id",
            "as": "categoryThreeId"
        }
    },
    {
        "$lookup": {
            "from": "shops",
            "localField": "shopId",
            "foreignField": "_id",
            "as": "shopId"
        }
    },
    {
        "$lookup": {
            "from": "users",
            "localField": "createdBy",
            "foreignField": "_id",
            "as": "createdBy"
        }
    },
    {
        "$lookup": {
            "from": "productcountschemas",
            "as": "selectedItemCount",
            "let": {
                "productId": "$_id"
            },
            "pipeline": [
                {
                    "$match": {
                        "$expr": {
                            "$and": [
                                {
                                    "$eq": [
                                        "$productId",
                                        "$$productId"
                                    ]
                                },
                                {
                                    "$eq": [
                                        "$createdBy",
                                        null
                                    ]
                                }
                            ]
                        }
                    }
                }
            ]
        }
    },
    {
        "$project": {
            "_id": 1,
            "originalPrice": 1,
            "skuId": 1,
            "geoLocation": 1,
            "variantIdsArr": 1,
            "productWeight": 1,
            "packageDimensions": 1,
            "productRating": 1,
            "productSellerId": 1,
            "productTags": 1,
            "productColor": 1,
            "productPriceUnit": 1,
            "productMaterial": 1,
            "productSleeveType": 1,
            "productPatternType": 1,
            "productCareType": 1,
            "dressStyle": 1,
            "quantity": 1,
            "createdAt": 1,
            "isProductInStock": 1,
            "isProductVariationExists": 1,
            "stockAvailability": 1,
            "productImages": 1,
            "globalTradeItemNumber": 1,
            "launchDate": 1,
            "productBrief": 1,
            "productDescription": 1,
            "productLength": 1,
            "productLengthUnit": 1,
            "productNeckStyle": 1,
            "productSize": 1,
            "productSizeIndicator": 1,
            "productTitle": 1,
            "slug": 1,
            "targetGender": 1,
            "brandId._id": 1,
            "brandId.brandName": 1,
            "brandId.brandNameSlug": 1,
            "categoryOneId._id": 1,
            "categoryOneId.categoryName": 1,
            "categoryOneId.categoryNameSlug": 1,
            "categoryTwoId._id": 1,
            "categoryTwoId.categoryName": 1,
            "categoryTwoId.categoryNameSlug": 1,
            "categoryThreeId._id": 1,
            "categoryThreeId.categoryName": 1,
            "categoryThreeId.categoryNameSlug": 1,
            "shopId._id": 1,
            "shopId.shopName": 1,
            "createdBy._id": 1,
            "createdBy.companyName": 1,
            "createdBy.name": 1,
            "dimensions._id": 1,
            "dimensions.dimensionName": 1,
            "ratings._id": 1,
            "ratings.rating": 1,
            "rate": {
                "$avg": "$ratings.rating"
            },
            "ratedByPeople": {
                "$size": "$ratings"
            },
            "selectedItemCount": "$selectedItemCount.itemCount",
            "productPrice": {
                "$map": {
                    "input": "$rentalContract",
                    "in": {
                        "$mergeObjects": [
                            {
                                "rentalContractId": "$$this._id"
                            },
                            {
                                "contractType": "$$this.contractType"
                            },
                            {
                                "price": {
                                    "$arrayElemAt": [
                                        "$productPrice.price",
                                        {
                                            "$indexOfArray": [
                                                "$productPrice.rentalContractId",
                                                "$$this._id"
                                            ]
                                        }
                                    ]
                                }
                            }
                        ]
                    }
                }
            }
        }
    },
    {
        "$skip": 0
    },
    {
        "$limit": 1
    },
    {
        "$sort": {
            "createdAt": -1
        }
    }
])

问题分析与解决方案 #

核心问题点 #

1. $match阶段的$elemMatch逻辑局限：$elemMatch只会筛选出存在至少一个符合条件的productPrice元素的文档，但不会过滤掉文档中不符合条件的productPrice元素，返回的文档仍会包含所有原始数组元素。
2. 空文档未被过滤：当前流程中，$filter后未处理productPrice被过滤为空的文档，这类无效文档仍会留在结果集中。
3. 排序与分页顺序错误：先执行$limit再执行$sort，只会对截取的1条数据排序，无法得到最新的目标数据。

修正后的聚合查询 #

db.products.aggregate([
    // 合并基础过滤，提前筛选符合条件的文档
    {
        "$match": {
            "isDeleted": false,
            "isArchived": false,
            "isProductDisplayAble": true,
            "createdBy": { "$ne": "6469a515159d16142e64b68e" },
            "productTags": { "$in": ["6456c1eb883d8e08841911ea"] },
            "productPrice": {
                "$elemMatch": {
                    "rentalContractId": ObjectId("64018dfc8a7d37f378d6c426"),
                    "price": { "$gte": 4, "$lte": 9 }
                }
            },
            "bookings": {
                "$not": {
                    "$elemMatch": {
                        "productBookingCompleteDate": { "$gte": "2023-05-21T04:59:01.105Z" },
                        "productBookingStartDate": { "$lte": "2023-05-21T04:59:01.105Z" }
                    }
                }
            }
        }
    },
    // 过滤productPrice数组，仅保留符合条件的元素
    {
        $addFields: {
            productPrice: {
                $filter: {
                    input: "$productPrice",
                    cond: { 
                        $and: [ 
                            { $eq: [ "$$this.rentalContractId", ObjectId("64018dfc8a7d37f378d6c426") ] }, 
                            { $gte: [ "$$this.price", 4 ] }, 
                            { $lte: [ "$$this.price", 9 ] } 
                        ] 
                    }
                }
            }
        }
    },
    // 过滤掉productPrice为空的无效文档
    {
        "$match": {
            "productPrice": { "$ne": [] }
        }
    },
    // 关联查询阶段
    {
        "$lookup": {
            "from": "rentalcontractdetails",
            "localField": "productPrice.rentalContractId",
            "foreignField": "_id",
            "as": "rentalContract",
            "pipeline": [
                { "$project": { "contractType": 1 } }
            ]
        }
    },
    { "$lookup": { "from": "brands", "localField": "brandId", "foreignField": "_id", "as": "brandId" } },
    { "$lookup": { "from": "dimensions", "localField": "productTags", "foreignField": "_id", "as": "dimensions" } },
    { "$lookup": { "from": "ratings", "localField": "_id", "foreignField": "productId", "as": "ratings" } },
    { "$lookup": { "from": "categoryones", "localField": "categoryOneId", "foreignField": "_id", "as": "categoryOneId" } },
    { "$lookup": { "from": "categorytwos", "localField": "categoryTwoId", "foreignField": "_id", "as": "categoryTwoId" } },
    { "$lookup": { "from": "categorythrees", "localField": "categoryThreeId", "foreignField": "_id", "as": "categoryThreeId" } },
    { "$lookup": { "from": "shops", "localField": "shopId", "foreignField": "_id", "as": "shopId" } },
    { "$lookup": { "from": "users", "localField": "createdBy", "foreignField": "_id", "as": "createdBy" } },
    {
        "$lookup": {
            "from": "productcountschemas",
            "as": "selectedItemCount",
            "let": { "productId": "$_id" },
            "pipeline": [
                {
                    "$match": {
                        "$expr": {
                            "$and": [
                                { "$eq": [ "$productId", "$$productId" ] },
                                { "$eq": [ "$createdBy", null ] }
                            ]
                        }
                    }
                }
            ]
        }
    },
    // 字段投影处理
    {
        "$project": {
            "_id": 1,
            "originalPrice": 1,
            "skuId": 1,
            "geoLocation": 1,
            "variantIdsArr": 1,
            "productWeight": 1,
            "packageDimensions": 1,
            "productRating": 1,
            "productSellerId": 1,
            "productTags": 1,
            "productColor": 1,
            "productPriceUnit": 1,
            "productMaterial": 1,
            "productSleeveType": 1,
            "productPatternType": 1,
            "productCareType": 1,
            "dressStyle": 1,
            "quantity": 1,
            "createdAt": 1,
            "isProductInStock": 1,
            "isProductVariationExists": 1,
            "stockAvailability": 1,
            "productImages": 1,
            "globalTradeItemNumber": 1,
            "launchDate": 1,
            "productBrief": 1,
            "productDescription": 1,
            "productLength": 1,
            "productLengthUnit": 1,
            "productNeckStyle": 1,
            "productSize": 1,
            "productSizeIndicator": 1,
            "productTitle": 1,
            "slug": 1,
            "targetGender": 1,
            "brandId._id": 1,
            "brandId.brandName": 1,
            "brandId.brandNameSlug": 1,
            "categoryOneId._id": 1,
            "categoryOneId.categoryName": 1,
            "categoryOneId.categoryNameSlug": 1,
            "categoryTwoId._id": 1,
            "categoryTwoId.categoryName": 1,
            "categoryTwoId.categoryNameSlug": 1,
            "categoryThreeId._id": 1,
            "categoryThreeId.categoryName": 1,
            "categoryThreeId.categoryNameSlug": 1,
            "shopId._id": 1,
            "shopId.shopName": 1,
            "createdBy._id": 1,
            "createdBy.companyName": 1,
            "createdBy.name": 1,
            "dimensions._id": 1,
            "dimensions.dimensionName": 1,
            "ratings._id": 1,
            "ratings.rating": 1,
            "rate": { "$avg": "$ratings.rating" },
            "ratedByPeople": { "$size": "$ratings" },
            "selectedItemCount": "$selectedItemCount.itemCount",
            "productPrice": {
                "$map": {
                    "input": "$rentalContract",
                    "in": {
                        "$mergeObjects": [
                            { "rentalContractId": "$$this._id" },
                            { "contractType": "$$this.contractType" },