compare two array elements with conditions in mongodb aggregation

Question

I have documents as

[{
        "id": "1",
        "base": 23,
        "dateValues": [{
            "timestamp": "2021-02-15T13:13:17.611Z",
            "value": 50
        }, {
            "timestamp": "2021-02-22T13:13:17.611Z",
            "value": 50
        }]
    },
    {
        "id": "2",
        "base": 256,
        "dateValues": [{
            "timestamp": "2021-02-01T13:13:17.611Z",
            "value": 50
        }, {
            "timestamp": "2021-02-10T13:13:17.611Z",
            "value": 50
        }]
    },
    {
        "id": "3",
        "base": 256,
        "dateValues": [{
            "timestamp": "2021-02-03T13:13:17.611Z",
            "value": 50
        }, {
            "timestamp": "2021-02-04T13:13:17.611Z",
            "value": 50
        }, {
            "timestamp": "2021-02-10T13:13:17.611Z",
            "value": 50
        }]
    },
    {
        "id": "4",
        "base": 256,
        "dateValues": [{
            "timestamp": "2021-02-03T13:13:17.611Z",
            "value": 50
        }, {
            "timestamp": "2021-02-08T13:13:17.611Z",
            "value": 50
        }]
    }
]

Now I want to compare dates of all objects. I'll simplify the logic as simply as I can step wise..

First I want to get all the distinct set of dates so these will be

"2021-02-01"
"2021-02-03"
"2021-02-04"
"2021-02-08"
"2021-02-15"
"2021-02-22"
"2021-02-10"

Now I want to loop above dates and compare each date with each objects dateValues

For example if we take first date "2021-02-01", I want only one date from each object(document)

who are less than or equal to "2021-02-01"
whose timestamp is latest timestamp
If no dateValue found then we take base value
Finally we will sum these values

so for this date "2021-02-01" dateValues will be

for id 1 no date value is there so in this case I need to take base value, which is 23
for id 2 we found date value so we will take its corresponding value, which is 50
for id 3 and 4 no date is matching so again we are taking its base value which 256 and 256
so final value comes for date "2021-02-01" is 23+50+256+256 = 585

Just another example for date "2021-02-22"

for id 1 we found date "2021-02-22", value is 50
for id 2 we found date "2021-02-10" value is 50
for id 3 we found date "2021-02-10" value is 50
for id 4 we found date "2021-02-08" value is 50
Final value for date "2021-02-22" is 200

Can we do this logic in mongo db aggregations? I could only get distinct dates but after that I lost it. Can anyone help me with this mongodb aggregation? or is it even possible to implement this logic in mongodb aggregation?

Ramit Mittal · Accepted Answer · 2021-04-12 17:16:21Z

Mongo aggregation framework has a lot of powerful operators and it basically boils down to chaining the stages and operators in correct order.

Assuming you have the documents in a collection named st.

Explanation:

Unwind and Group will give you one document for each date
Lookup all the documents in the same collection for each date
Apply the pipeline inside lookup to all documents during lookup finding the dateValue that matches the date or returning the base.
Sum the records

[
    { $unwind: "$dateValues" },
    { $group: {
        _id: "$dateValues.timestamp"
    }},
    { $lookup: {
        from: "st",
        as: "records",
        let: { date: "$_id" },
        pipeline: [
            { $project: {
                base: 1,
                dateValues: {
                    $filter: {
                        input: "$dateValues",
                        as: "dateValue",
                        cond: {
                            $eq: ["$$dateValue.timestamp", "$$date"]
                        }
                    }
                }
            }},
            { $project: {
                val: { $cond: {
                    if: {$eq: [ {$size: "$dateValues"}, 1]},
                    then: "$dateValues.0.value",
                    else: "$base"
                }}
            }},
        ],
    }},
    { $project: {
        _id: 1,
        finalValue: {
            $sum: "$records.val"
        }
    }}
]

Results:

{ "_id" : "2021-02-22T13:13:17.611Z", "finalValue" : 768 }
{ "_id" : "2021-02-01T13:13:17.611Z", "finalValue" : 535 }
{ "_id" : "2021-02-15T13:13:17.611Z", "finalValue" : 768 }
{ "_id" : "2021-02-04T13:13:17.611Z", "finalValue" : 535 }
{ "_id" : "2021-02-10T13:13:17.611Z", "finalValue" : 279 }
{ "_id" : "2021-02-03T13:13:17.611Z", "finalValue" : 279 }
{ "_id" : "2021-02-08T13:13:17.611Z", "finalValue" : 535 }

Note: Please double check the $filter and $cond and ensure that it matches your requirements.

Updated pipeline: Added a reduce stage

The filter stage selects all the dateValues with timestamp <= the date.
The reduce stage selects the largest dateValue amongst them.

[
        { $unwind: "$dateValues" },
        { $group: {
            _id: "$dateValues.timestamp"
        }},
        { $lookup: {
            from: "st",
            as: "records",
            let: { date: "$_id" },
            pipeline: [
                { $project: {
                    base: 1,
                    dateValues: {
                        $filter: {
                            input: "$dateValues",
                            as: "dateValue",
                            cond: { $lte: ["$$dateValue.timestamp", "$$date"] },
                        }
                    }
                }},
                { $project: {
                    base: 1,
                    dateValues: {
                        $reduce: {
                            input: "$dateValues",
                            initialValue: null,
                                in: { $cond: {
                                    if: { $gt: ["$$this.timestamp", "$$value.timestamp"] },
                                    then: "$$this",
                                    else: "$$value"
                                }}
                        }
                    }
                }},
                { $project: {
                    val: { $cond: {
                        if: {$eq: ["$dateValues", null]},
                        then: "$base",
                        else: "$dateValues.value",
                    }}
                }},
            ],
        }},
        { $project: {
            _id: 1,
            finalValue: {
                $sum: "$records.val"
            }
        }}
]

Thanks for the help @Ramit.. Really appreciated :) I tried this but Its not taking latest date. I mean if we are taking date "2021-02-22" in comparison it should take date which is less than equal and it should have latest timestamp. Currently we are just taking first array element. I tried sorting array and grouping it back but then sum is not happening correctly..
Im sorry but dates are coming same after comparing and also "val" is getting overwritten by last object so calculations for "val" is same for all.

Collectives™ on Stack Overflow

compare two array elements with conditions in mongodb aggregation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related