Using result of aggregation pipeline to update

I want to run an aggregation pipeline, use the result as filter to find a document and update it.

My schema has three fields : name, shiftWorked, eligible.

  1. I want to find an employee who has worked at least 10 shifts (should have 10 entries against his name) and should not have worked more than 5 shifts in last 3 days.
  2. This will give me certain employee names.
  3. i want to pick first such eligible employee and mark him as eligible.

Please pardon my ignorance, i am super new to mongoDB, but I think I’ll need two aggregations, first will filter the employees based number of shifts worked and second will filter it further to makes sure employee has not worked more than 5 shifts in last three days.
If i can get these name in a query, I can use findOneAndUpdate to pick one such employee and mark him as eligible.

Just wanted to know what is the best way to go about this?

Cheers!

You can get the results of employees in a single aggregation. To find count, you can checkout $count or $group with $sum:1 (will be given in examples of $group). The first stage is to find the counts of shifts of each user, (1 for total count, 1 for last 5 days count). You can use conditions in count when doing $group. Checkout $sum and $cond and use them together in $group.

Using this, you can find the employees matching point 1 and 2. Then the next stage in the same aggregation you can limit the result to first document matching the count. use $match to filter the count threshold, then $limit to limit to 1 document.

At last you can use $out to update that particular employee in the collection to mark him eligible.
In total you have 1 aggregation only. Sample -

Assuming the data model to be -

{
    "name": "user 1",
    "shiftWorked": ISODate("2020-01-01"), // some date field.
    "eligible": false
}

Aggregation query could look like -

[
    {
        "$group": {
            "_id": "name", // grouping condition.
            "counts": {
                "total": {
                    "$sum": 1
                },
                "last5days": {
                    "$sum": {
                        "$cond": [
                            {"$gte": ["$shiftWorked", "{{3 day's before date, pre calculate this in code and insert.}}"]}, // shift worked should be greater than equal to 3 day's old date.
                            1, // true case.
                            0 // false case.
                        ]
                    }
                }
            }
        }
    },
    {
        "$match": {
            "counts.total": {
                "$gte": 10
            },
            "counts.last5days": {
                "$gte": 5
            }
        }
    },
    {
        // limiting to first document. (no sort order)
        "$limit": 1
    },
    {   //updating it back into collection. (MongoDB v4.4 minimum if same collection.)
        "$merge": {
            "into": "collectionName",
            "on": "_id",
            "whenMatched": "merge",
            // customize this stage.
        }
    }
]
1 Like

Thanks maestro!! :smiley:

Hey Shrey,

Are aggregation pipelines atomic? In the sense that, while we are executing this pipeline can there be an update in the background which makes the selected employee ineligible? Reason I am asking this is because, merge with same collection cannot be used with transactions (which I just checked after reading your answer).

Yes, an aggregation using $merge cannot be used inside a transaction. To do that, you might skip the $merge operation, get the result back in application code and do an update operation.