CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
user12883228
user12883228

Reputation: 41

Mongodb aggregate $group for non-existing items

I have document like this :

Documents :

{score: 1, value: 10}
{score: 3, value: 10}
{score: 1, value: 10}
{score: 4, value: 10}
{score: 1, value: 10}
{score: 5, value: 10}
{score: 5, value: 10}
{score: 10, value: 10}

In this collection, there is no score for 2,6,7,8,9 but I need output like below.

Output :

{score: 1, avg: 10}
{score: 2, avg: 0}
{score: 3, avg: 10}
{score: 4, avg: 10}
{score: 5, avg: 10}
{score: 6, avg: 0}
{score: 7, avg: 0}
{score: 8, avg: 0}
{score: 9, avg: 0}
{score: 10, avg: 10}

Any option in Mongo aggregate which will generate this. Please assist

Upvotes: 1

Views: 380

Answers (2)

matthPen
matthPen

Reputation: 4343

Assuming you know the range of scores, there's a trick to achieve exactly what you want :

1 - Insert in your collection a document for each score, with value field not set or set to null : 

db.collection.insertMany([
  {
    score: 1,
  },
  {
    score: 2,
  },
  {
    score: 3,   
  },
  {
    score: 4,    
  },
  {
    score: 5,   
  },
  {
    score: 6,  
  },
  {
    score: 7,    
  },
  {
    score: 8,    
  },
  {
    score: 9, 
  },
  {
    score: 10,
  }
]);

It's important for value field not to be set, because a value set at 0 will affect average calculation

Of course this operation must be performed only once.

Then you can apply the following aggregation, which will output exactly what you need : 

db.collection.aggregate([
  {
    $bucket: {
      groupBy: "$score",
      boundaries: [
        0,
        1,
        2,
        3,
        4,
        5,
        6,
        7,
        8,
        9,
        10,
        11
      ],
      output: {
        avg: {
          $avg: "$value"
        }
      }
    }
  },
  {
    $project: {
      score: "$_id",
      avg: {
        $ifNull: [
          "$avg",
          0
        ]
      },
      _id: 0
    }
  }
])

Will output : 

[
  {
    "avg": 10,
    "score": 1
  },
  {
    "avg": 0,
    "score": 2
  },
  {
    "avg": 10,
    "score": 3
  },
  {
    "avg": 10,
    "score": 4
  },
  {
    "avg": 10,
    "score": 5
  },
  {
    "avg": 0,
    "score": 6
  },
  {
    "avg": 0,
    "score": 7
  },
  {
    "avg": 0,
    "score": 8
  },
  {
    "avg": 0,
    "score": 9
  },
  {
    "avg": 10,
    "score": 10
  }
]

You can test it here.

Upvotes: 0

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

You can try that using aggregation :

db.collection.aggregate([
    { $group: { _id: '$score', avg: { $avg: '$value' } } },
    { $group: { _id: '', min: { $min: '$_id' }, max: { $max: '$_id' }, data: { $push: '$$ROOT' } } },
    { $project: { _id: 0, data: 1, nums: { $range: ['$min', "$max", 1] } } },
    { $project: { data: { $concatArrays: ["$data", { $map: { input: { $setDifference: ["$nums", "$data._id"] }, in: { _id: '$$this', avg: 0 } } }] } } },
    { $unwind: '$data' }, { $replaceRoot: { newRoot: "$data" } }
])

Test : MongoDB-Playground

Upvotes: 3

Related Questions