Is it possible to make a string date comparision inside the mongo $filter cond operator?

Question

In this case, to fetch more compact data from MongoDB, I need to filter subdocument (records) by date value that has a string type. As you can see below, the record document is a nested array.

[
{
    "_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": []
        }]
    }
}]

When I try to achieve this goal by using the script below, I have encountered some issues listed below.

• ConversionFailure (code:241): I think $dateFromString couldn't consume "$$record.date" filter value. It is working when I use it without $dateFromString.
• LocationError (code:31261): While using the $function to compare dates, the cond argument of $function throws an error like this. So, I couldn't use a function too.

        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"
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            }
        ])

I really didn't find a solution to compare these dates inside $filter cond to complete my requirement. What are the possible solutions? Thanks

Answer 1

I have solved my problem myself. It may be a workaround but it works now. In the $filter operator cond attribute needs the same date object to compare. However, the parsing is impossible for $filter reference values, due to the methods weren't able to read them. So at this point, to do this, we need to use the $convert operator as below.

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',
            },
        },
    ]
}