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..

1. 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"

2. 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?

Answer 1

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