MongoDB Get average of group considering rank of document

Question

I have documents getting in order like:

{ 
   "_id": "abcde1",
   "value" : 300
},
{ 
   "_id": "abcde2",
   "value" : 200
},
{ 
   "_id": "abcde3",
   "value" : 400
},
{ 
   "_id": "abcde4",
   "value" : 500
},
{ 
   "_id": "abcde5",
   "value" : 600
}

i.e, I want average of "_id" of first 2, first 4 and all 5 documents matching like in single query:

{
    "value_2" : 250,   // Average of first 2 documents
    "value_4" : 350,    // Average of first four documents
    "value_5" : 400     // Average of all 5 documents
}

Is it possible to Group documents based on rank of document.

I can do 3 results in 3 separate queries. Is it possible in single query?

Answer 1

You could use a $facet aggregation stage:

// { _id: "abcde1", value: 300 }
// { _id: "abcde2", value: 200 }
// { _id: "abcde3", value: 400 }
// { _id: "abcde4", value: 500 }
// { _id: "abcde5", value: 600 }
db.collection.aggregate([

  { $facet: {
    value_2: [ { $limit: 2 }, { $group: { _id: null, value_2: { $avg: "$value" } } } ],
    value_4: [ { $limit: 4 }, { $group: { _id: null, value_4: { $avg: "$value" } } } ],
    value_5: [ { $limit: 5 }, { $group: { _id: null, value_5: { $avg: "$value" } } } ]
  }},
  // {
  //   value_2: [ { _id: null, value_2: 250 } ],
  //   value_4: [ { _id: null, value_4: 350 } ],
  //   value_5: [ { _id: null, value_5: 400 } ]
  // }

  { $set: {
    value_2: { $first: "$value_2.value_2" },
    value_4: { $first: "$value_4.value_4" },
    value_5: { $first: "$value_5.value_5" }
  }}
])
// { "value_2" : 250, "value_4" : 350, "value_5" : 400 }

The $facet stage allows us to run multiple aggregation pipelines within a single stage on the same set of input documents. Each sub-pipeline has its own field in the output document where its results are stored as an array of documents.

Each field is thus produced by its own aggregation pipeline whose first stage is a simple $limit, followed by a $group stage that'll produce the $avg (average) of all considered documents.

The second part of the pipeline (the $set stage) is just there to clean-up the $facet output to the format you wished for.

Answer 2

You could try running the following pipeline:

db.collection.aggregate([
    // previous pipeline here
    {
        "$group": {
            "_id": null,
            "values": { "$push": "$value" }                
        }
    },
    { "$unwind": { "path": "$values", "includeArrayIndex": "rank"  } },
    {
        "$group": {
            "_id": null,
            "value_2_sum": {
                "$sum": { 
                    "$cond": [
                        { "$lt": ["$rank", 2] },
                        "$values",
                        0
                    ]
                }
            },
            "value_2_count": {
                "$sum": { 
                    "$cond": [
                        { "$lt": ["$rank", 2] },
                        1,
                        0
                    ]
                }
            },
            "value_4_sum": {
                "$sum": { 
                    "$cond": [
                        { "$lt": ["$rank", 4] },
                        "$values",
                        0
                    ]
                }
            },
            "value_4_count": {
                "$sum": { 
                    "$cond": [
                        { "$lt": ["$rank", 4] },
                        1,
                        0
                    ]
                }
            },
            "value_5": { "$avg": "$values" }
        }
    },
    {
        "$project": {
            "value_2" : { "$divide": ["$value_2_sum", "$value_2_count"] },   // Average of first 2 documents
            "value_4" : { "$divide": ["$value_4_sum", "$value_4_count"] },    // Average of first four documents
            "value_5" : 1
        }
    }
])