How to calculate field value based on previous documents?

Question

MongoDB

Hello!
There is the following collection:

---- -------- -------- --------
  id   name     active   rating
 ---- -------- -------- --------
  0    User 0   0        8740
  1    User 1   1        7900
  2    User 2   1        3230
  3    User 3   1        9790
  4    User 4   1        3230
  5    User 5   1        1540
 ---- -------- -------- --------

It is necessary to assign seats based on the rating and the activity flag:

---- -------- -------- -------- ----------
  id   name     active   rating   position
 ---- -------- -------- -------- ----------
  3    User 3   1        9790     1
  0    User 0   0        8740     2
  1    User 1   1        7900     2
  2    User 2   1        3230     3
  4    User 4   1        3230     3
  5    User 5   1        1540     5
 ---- -------- -------- -------- ----------

Now this table is generated using the code:

$activePosition = 1;
$overallPosition = 1;

$prevActiveItem = null;
$prevInactiveItem = null;
foreach ($items as &$item) {
    if ($item['active']) {
        $position = &$activePosition;
        $prevItem = $prevActiveItem;
        $prevActiveItem = $item;

        $overallPosition++;
    } else {
        $position = &$overallPosition;
        $prevItem = $prevInactiveItem;
        $prevInactiveItem = $item;
    }

    if ($prevItem && $prevItem['rating'] == $item['rating']) {
        $item['position'] = $prevItem['position'];
    } else {
        $item['position'] = $position;
    }

    $position++;
}

unset($item);

Interested in whether it is possible to arrange positions using the aggregation framework?

Answer 1

In Mongo Aggregation can be done through a temporary array.
1. Sort all documents in descending order of rating and active (the position will increase from top to bottom)
2. $push all sorted documents into a temporary data
array 3. Create a new arr array and add all elements of the data array to it in the same order, but adding the position field , which is formed based on the position of the object in the data array ( $ indexOfArray )
5. Converting documents after splitting to the desired form

db.getCollection('testo').aggregate([
    { $sort: { rating: -1, active: -1 } },
    { $group: { _id: null, data: { $push: "$$ROOT" } } },
    {
        $project: {
            arr: {
                $map: {
                    input: "$data", as: "e", in: {
                        rating: "$$e.rating", 
                        active: "$$e.active", 
                        position: {
                            $add: [{ $indexOfArray: ["$data", "$$e"] }, 1]
                        }
                    }
                }
            }
        }
    },
    { $unwind: "$arr" },
    { $project: { 
        _id: 0,
        rating: "$arr.rating", 
        active: "$arr.active", 
        position: "$arr.position" } }
])

If the version of MongoDB is quite old, then you will have to manually enter all the fields that need to be taken into account in the array. If the version is new, you can add the $mergeObjects action . There is also an option to use more complex schemes ( $arrayToObject , $objectToArray ) to save fields when the array transitions from one state to another