MongoDB $bucketAuto Aggregate

Working with Data
1

Hello Team,

Below is my data set,

db.Test.insert({ "_id": 1, "Name": "A" });
db.Test.insert({ "_id": 2, "Name": "B" });
db.Test.insert({ "_id": 3, "Name": "C" });
db.Test.insert({ "_id": 4, "Name": "C" });
db.Test.insert({ "_id": 5, "Name": "D" });
db.Test.insert({ "_id": 6, "Name": "E" });
db.Test.insert({ "_id": 7, "Name": "F" });
db.Test.insert({ "_id": 8, "Name": "G" });
db.Test.insert({ "_id": 9, "Name": "H" });
db.Test.insert({ "_id": 10, "Name": "I" });

Aggregate Query

db.Test.aggregate( [
  {
    $bucketAuto: {
      groupBy: "$_id",
      buckets: 5,
      output: {"Unique": {"$addToSet": "$_id"}},
    }
  }
] )

Results

{ "_id" : { "min" : 1, "max" : 3 }, "Unique" : [ 1, 2 ] }

{ "_id" : { "min" : 3, "max" : 5 }, "Unique" : [ 3, 4 ] }

{ "_id" : { "min" : 5, "max" : 7 }, "Unique" : [ 6, 5 ] }

{ "_id" : { "min" : 7, "max" : 9 }, "Unique" : [ 7, 8 ] }

{ "_id" : { "min" : 9, "max" : 10 }, "Unique" : [ 9, 10 ] }

How can I also add a new field and enable auto incrementing counter.

{ "_id" : { "min" : 1, "max" : 3 }, "Unique" : [ 1, 2 ] , "Counter": 1}

{ "_id" : { "min" : 3, "max" : 5 }, "Unique" : [ 3, 4 ], "Counter": 2 }

{ "_id" : { "min" : 5, "max" : 7 }, "Unique" : [ 6, 5 ],"Counter": 3 }

{ "_id" : { "min" : 7, "max" : 9 }, "Unique" : [ 7, 8 ] , "Counter": 4}

{ "_id" : { "min" : 9, "max" : 10 }, "Unique" : [ 9, 10 ] , "Counter": 5}

Basically every bucket should be assigned a counter with incrementing value. How can I achieve this mongo aggregate 4.4 ?

Regards,
Rama

2

Hello, @Laks !

In order to add additional field with auto incrementing value, you need to add couple more stages to your aggregation pipeline:

db.Test.aggregate([
  {
    $bucketAuto: {
      groupBy: '$_id',
      buckets: 5,
      output: {
        unique: { 
          $addToSet: '$_id'
        },
      },
    }
  },
  {
    // first we group the buckets, so we could loop through each bucket
    // and calculate 'counter' value for it
    $group: {
      _id: null,
      buckets: {
        $push: {
          _id: '$_id',
          unique: '$unique'
        }
      }
    }
  },
  {
    $project: {
      result: {
        // loop through buckets
        $reduce: {
          input: '$buckets',
          initialValue: {
            i: 0,
            buckets: [],
          },
          in: {
            i: {
              $add: ['$$value.i', 1]
            },
            buckets: {
              $concatArrays: ['$$value.buckets', [{
                _id: '$$this._id',
                unique: '$$this.unique',
                counter: {
                  $add: ['$$value.i', 1]
                },
              }]]
            }
          }
        }
      }
    }
  },
  // ungroup buckets
  {
    $unwind: '$result.buckets',
  },
  // restore initial structure
  {
    $project: {
      _id: '$result.buckets._id',
      unique: '$result.buckets.unique',
      counter: '$result.buckets.counter',
    }
  }
]);

Output:

[
  { _id: { min: 1, max: 3 }, unique: [ 1, 2 ], counter: 1 },
  { _id: { min: 3, max: 5 }, unique: [ 4, 3 ], counter: 2 },
  { _id: { min: 5, max: 7 }, unique: [ 6, 5 ], counter: 3 },
  { _id: { min: 7, max: 9 }, unique: [ 7, 8 ], counter: 4 },
  { _id: { min: 9, max: 10 }, unique: [ 10, 9 ], counter: 5 }
]
1 Like