Mongodb 比较两个字段与匹配字段值聚合 [英] Mongodb compare two fields with matched fields value aggregate

问题描述

我的查询有问题，我传递的参数是凭证 ID 和用户 ID，我需要检查该用户是否超过了 LimitedQuantityPerUser.如果是，请排除此凭证.但是我在比较匹配字段时遇到问题，例如，如果凭证 ID 是 'aaa'，请检查 quantityBrought > limitedQuantityPerUser.

I am having problems in my query, The parameter I am passing are voucherId, and userId, I need to check if that user exceeded the limitedQuantityPerUser. If yes, exclude this voucher. But I am having problems comparing the matching fields, example if voucherId is 'aaa', check if the quantityBrought > limitedQuantityPerUser.

我已经试过了

vouchers.find({
         'status': {
          $in: ['OP', 'WG', 'LO', 'SO']
        },
        'details.userReachedLimitedQuantity': {
          $ne: userId
        },
      }, fields, {
        sort: {
          'order': 1
        }
      }

但这会给我所有的结果，其中 userId 在列表中不相等.不完全是我需要的.使用聚合求解更好吗?

But this will give me all the result where userId not equal in the list. not exactly what I need. Is it better to solve using aggregate?

示例资源 JSON 文件

Example resource JSON file

[{
        "_id": "111",
        "details": {
            "limitedQuantityPerUser": "3",
            "userReachedLimitedQuantity": [{
                    "userId": "aaa",
                    "quantityBrought": "2"
                },
                {
                    "userId": "bbb",
                    "quantityBrought": "1"
                },
                {
                    "userId": "ccc",
                    "quantityBrought": "3"
                }
            ],
            "status": "OP"
        }
    },
    {
        "_id": "222",
        "details": {
            "limitedQuantityPerUser": "2",
            "userReachedLimitedQuantity": [{
                    "userId": "eee",
                    "quantityBrought": "2"
                },
                {
                    "userId": "bbb",
                    "quantityBrought": "1"
                },
                {
                    "userId": "vvv",
                    "quantityBrought": "2"
                }
            ],
            "status": "OP"
        }
    }
]

推荐答案

您可以使用下面的聚合来检查指定的 userId 是否超出了定义的限制.

You can use below aggregation to check whether specified userId exceeded defined limit.

db.vouchers.aggregate([
    {
        $match: {
            $expr: {
                $allElementsTrue: {
                    $map: {
                        input: "$details.userReachedLimitedQuantity",
                        as: "user",
                        in: {
                            $or: [
                                { $ne: [ "$$user.userId", userId ] },
                                { $lte: [ "$$user.quantityBrought", "$details.limitedQuantityPerUser"  ] }
                            ]
                        }
                    }
                }
            }
        }
    }
])

$allElementsTrue 将布尔值数组作为范围.您可以使用 $map 来转换您的 userReachedLimitedQuantity 到检查 userId 和 quantityBrought 字段的布尔值.

$allElementsTrue takes an array of boolean values as a parameter. You can use $map to transform your userReachedLimitedQuantity into such boolean values checking userId and quantityBrought fields.

因此，如果任何元素为 false 则表示该用户的 quantityBrought 大于 limitedQuantityPerUser

So if any element is false then it means that that user's quantityBrought is greater than limitedQuantityPerUser

这篇关于Mongodb 比较两个字段与匹配字段值聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！