Reshape MongoDB aggregation result to field value documents

Question

I have a data collection containing data in the following shape:

[ {_id: "1",
   aa1: 45, aa2: 56,
   bb1: 90, bb2: 78,
   cc1: 34, cc2: 98 },
  {_id: "2",
   aa1: 76, aa2: 56,
   bb1: 45, bb2: 67,
   cc1: 75, cc2: 87 } ]

on this data I perform a MongoDB aggregation pipeline:

db.colleciton.aggregate([
 { $project: {
             "_id": "$_id",
             "aaa": { $avg: ["$aa1","$aa2"] },
             "bbb": { $avg: ["$bb1","$bb2"] },
             "ccc": { $avg: ["$cc1","$cc2"] } 
            }
 }, 
 { $group: {
             "_id": "AvgCalc",
             "aaa": { $avg: "$aaa" },
             "bbb": { $avg: "$bbb" },
             "ccc": { $avg: "$ccc" } 
            }     
 }
])

that gives my result in the format:

[ {_id: "AvgCalc",
   aaa: 67,
   bbb: 55,
   ccc: 90} ]

I would like to convert this in the aggregation to give my result as:

[ {field: "aaa", value: 67}, 
  {field: "bbb", value: 55},
  {field: "ccc", value: 90} ]

Is there a way to achieve this with aggregation? I would like to add this as a final stage in the pipeline. I tried adding a $project stage but with no success.

`I have data`. What do you mean by that? Is that a single document or a couple of documents in your collection? Provide structure of single document in your collection on which aggregation is performed. You can't use `$avg` operator like that in your aggregation pipeline. It's supposed to be **unary** operator if in `$group` stage. Check [documentation](https://docs.mongodb.com/manual/reference/operator/aggregation/avg/). Provide valid aggregation pipeline for your documents and exact result of that pipeline. — mr.tarsa, Aug 08 '16 at 11:01
sorry, my bad. I'm working with a data set with 80 fields so just trying to simplify the example. Made a when I simplified the pipeline. — Gerrie van Wyk, Aug 08 '16 at 12:12

score 5 · Accepted Answer · answered Aug 08 '16 at 12:27

This pipeline should give you the desired result

db.getCollection('yourColl').aggregate([
 { 
    $project: {
        "_id": "$_id",
        "aaa": { $avg: ["$aa1","$aa2"] },
        "bbb": { $avg: ["$bb1","$bb2"] },
        "ccc": { $avg: ["$cc1","$cc2"] } 
    }
 }, 
 { 
    $group: {
        "_id": "AvgCalc",
        "aaa": { $avg: "$aaa" },
        "bbb": { $avg: "$bbb" },
        "ccc": { $avg: "$ccc" } 
    }     
 },
 {
    $project: {
        "items": [ 
            {name: {$literal: "aaa"}, value: "$aaa"},
            {name: {$literal: "bbb"}, value: "$bbb"},
            {name: {$literal: "ccc"}, value: "$ccc"}
        ]
    }
 },
 {
     $unwind: {
         path: "$items"
     }
 },
 {
     $project: {
         _id: 0,
         field: "$items.name",
         value: "$items.value"
     }
 }
])

For your sample data the previous output was

/* 1 */
{
    "_id" : "AvgCalc",
    "aaa" : 58.25,
    "bbb" : 70.0,
    "ccc" : 73.5
}

and with the new pipeline the output is like this

/* 1 */
{
    "field" : "aaa",
    "value" : 58.25
}

/* 2 */
{
    "field" : "bbb",
    "value" : 70.0
}

/* 3 */
{
    "field" : "ccc",
    "value" : 73.5
}

Seriously impressive! – Gerrie van Wyk Aug 08 '16 at 12:59 — Gerrie van Wyk, Aug 08 '16 at 12:59

Reshape MongoDB aggregation result to field value documents

1 Answers1