mongodb - 是否可以在 mongo $filter cond 运算符中进行字符串日期比较？

Question

在这种情况下，为了从 MongoDB 中获取更紧凑的数据，我需要records按具有字符串类型的日期值过滤子文档 ()。如下所示，record文档是一个嵌套数组。

[
{
    "_id": 14,
    "isActive": true,
    "name": "D.HONGKONG-1",
    "factory_id": 10,
    "factory_name": "CHAOI",
    "branches":
    {
        "_id": 205,
        "isActive": true,
        "name": "DZZ NUCE",
        "region_id": 14,
        "owner_name": "A",
        "phone": "",
        "records": [
        {
            "date": "24-10-2020",
            "sales": [
            {
                "time": "17:58",
                "explanation": "DAILY CALCULATION",
                "type": "DAILY",
                "quantity":
                {
                    "P": 0,
                    "K": 0,
                    "U": 0
                }
            }],
            "stocks": [
            {
                "time": "17:58",
                "explanation": "DELIVERY COMPL",
                "type": "DELIVERY",
                "quantity":
                {
                    "P": 0,
                    "K": 0,
                    "U": 0
                }
            },
            {
                "time": "17:58",
                "explanation": "DAILY S. ENTRY",
                "type": "DAILY",
                "quantity":
                {
                    "P": 0,
                    "K": 0,
                    "U": 0
                }
            }],
            "delivery":
            {
                "P": 0,
                "K": 0,
                "U": 0
            },
            "material": []
        },
        {
            "date": "23-10-2020",
            "sales": [
            {
                "time": "17:58",
                "explanation": "",
                "type": "DAILY",
                "quantity":
                {
                    "P": 0,
                    "K": 0,
                    "U": 0
                }
            }],
            "stocks": [
            {
                "time": "17:58",
                "explanation": "",
                "type": "DELIVERY",
                "quantity":
                {
                    "P": 0,
                    "K": 0,
                    "U": 0
                }
            },
            {
                "time": "17:58",
                "explanation": "",
                "type": "DAILY",
                "quantity":
                {
                    "P": 0,
                    "K": 0,
                    "U": 0
                }
            }],
            "delivery":
            {
                "P": 0,
                "K": 0,
                "U": 0
            },
            "material": []
        }]
    }
}]

当我尝试使用下面的脚本来实现这个目标时，我遇到了下面列出的一些问题。

• ConversionFailure（代码：241）：我认为$dateFromString无法使用"$$record.date"过滤器值。当我在没有$dateFromString.
• LocationError (code:31261)：使用$function比较日期时， 的cond参数会$function抛出这样的错误。所以，我也不能使用函数。

        aggregate([{
                $match: {
                    factory_id: parseInt(factoryId),
                    isActive: true
                }
            },
            {
                $lookup: {
                    from: 'branches',
                    localField: '_id',
                    foreignField: 'region_id',
                    as: 'branches',
                },
            },
            {
                $unwind: {
                    path: '$branches'
                }
            },
            {
                $project: {
                    name: 1,
                    factory_id: 1,
                    factory_name: 1,
                    isActive: 1,
                    // 'order': 1,
                    'branches._id': 1,
                    'branches.name': 1,
                    'branches.isActive': 1,
                    'branches.region_id': 1,
                    'branches.owner_name': 1,
                    'branches.phone': 1,
                    'branches.records': {
                        $filter: {
                            input: '$branches.records',
                            as: 'record',
                            cond: {

                                $eq: [{
                                        $dateFromString: {
                                            dateString: "11-11-2021",
                                            format: "%d-%m-%Y"
                                        }
                                    },
                                    {
                                        $dateFromString: {
                                            dateString: "$$record.date",
                                            format: "%d-%m-%Y"
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            }
        ])

我真的没有找到一个解决方案来比较这些日期$filter cond来完成我的要求。有哪些可能的解决方案？谢谢

标签： mongodbmongooseaggregation-frameworkaggregatemongodate

Answer 1

我自己解决了我的问题。这可能是一种解决方法，但现在可以使用。在$filter操作符cond属性中需要同一个日期对象进行比较。$filter但是，由于方法无法读取参考值，因此无法解析参考值。所以此时，要做到这一点，我们需要使用$convert下面的运算符。

cond: {
    $gte: [{
            $dateFromString: {
                dateString: lastDate.format('DD-MM-YYYY'),
                format: '%d-%m-%Y',
            },
        },
        {
            $dateFromString: {
                dateString: {
                    $convert: {
                        input: '$$record.date',
                        to: 'string',
                    },
                },
                format: '%d-%m-%Y',
            },
        },
    ]
}