How do I merge results of a MongoDB $group in aggregation pipeline back into the original documents?-CodePudding

I have a collection that looks like this:

{ "level" : "6", "x" : 2.4, "y" : 8.3 }  
{ "level" : "6", "x" : 0.1, "y" : 1.1 }  
{ "level" : "6", "x" : 9.4, "y" : 4.6 }  
{ "level" : "4", "x" : 3.3, "y" : 8.1 }
{ "level" : "4", "x" : 2.7, "y" : 3.3 } 
{ "level" : "4", "x" : 6.3, "y" : 0.5 }

I'd like to calculate the $max (and $min) for each $x and $y within (groupBy) each $level and return those results as new fields into the original documents for the next stage in an aggregation pipeline.

Desired output:

{ "level" : "6", "x" : 2.4, "y" : 8.3, "max_level_6_x": 9.4, "max_level_6_y": 8.3}  
{ "level" : "6", "x" : 0.1, "y" : 1.1, "max_level_6_x": 9.4, "max_level_6_y": 8.3}  
{ "level" : "6", "x" : 9.4, "y" : 4.6, "max_level_6_x": 9.4, "max_level_6_y": 8.3}  
{ "level" : "4", "x" : 3.3, "y" : 8.1, "max_level_4_x": 6.3, "max_level_4_y": 8.1}  
{ "level" : "4", "x" : 2.7, "y" : 3.3, "max_level_4_x": 6.3, "max_level_4_y": 8.1} 
{ "level" : "4", "x" : 6.3, "y" : 0.5, "max_level_4_x": 6.3, "max_level_4_y": 8.1}

I managed to set up the first part of the aggregation pipeline:

.aggregate([
          {
            $group: {
              _id: '$level',
              min_x: { $min: '$x' },
              max_x: { $max: '$x' },
              min_y: { $min: '$y' },
              max_y: { $max: '$y' }
            },
          },
        ]
...

And this gave me correct results for the grouping part, grouped by the $level, however I am not sure how to merge these results back into the documents for each $level as shown above.

CodePudding user response：

You can use a sub-pipeline to perform the $group and do some wrangling to get your expected form.

db.collection.aggregate([
  {
    "$lookup": {
      "from": "collection",
      "localField": "level",
      "foreignField": "level",
      "pipeline": [
        {
          $group: {
            _id: "$level",
            min_x: {
              $min: "$x"
            },
            max_x: {
              $max: "$x"
            },
            min_y: {
              $min: "$y"
            },
            max_y: {
              $max: "$y"
            }
          }
        }
      ],
      "as": "aggregation"
    }
  },
  {
    "$unwind": "$aggregation"
  },
  // cosmetics / wrangling
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$$ROOT",
          {
            max_x: "$aggregation.max_x",
            max_y: "$aggregation.max_y"
          }
        ]
      }
    }
  },
  {
    $unset: "aggregation"
  }
])

Mongo Playground