mongodb aggregation with array

Question

I have data like this:

{
    "_id" : ObjectId("..."),
    "name" : "Entry 1",
    "time" : ISODate("2013-12-28T06:00:00.000Z"),
    "value" : 100
},
{
    "_id" : ObjectId("..."),
    "name" : "Entry 2",
    "time" : ISODate("2013-12-28T06:00:00.000Z"),
    "value" : 200
},
{
    "_id" : ObjectId("..."),
    "name" : "Entry 1",
    "time" : ISODate("2013-12-28T11:00:00.000Z"),
    "value" : 110
},
{
    "_id" : ObjectId("..."),
    "name" : "Entry 2",
    "time" : ISODate("2013-12-28T11:00:00.000Z"),
    "value" : 230
},
{
    "_id" : ObjectId("..."),
    "name" : "Entry 3",
    "time" : ISODate("2013-12-28T11:00:00.000Z"),
    "value" : 25
},
{
    "_id" : ObjectId("..."),
    "name" : "Entry 4",
    "time" : ISODate("2013-12-28T11:00:00.000Z"),
    "value" : 15
}

I need the result grouped by time with percentage for each entry like this (group entries by volume "others" when entries for time period more than two, but it's not necessary):

{
   "_id": ISODate("2013-12-28T11:00:00.000Z"),
   "entries": [
      {
         "name": "Entry 1",
         "percentage": 33.3
      },
      {
         "name": "Entry 2",
         "percentage": 66.6
      },

   ]
},
{
   "_id": ISODate("2013-12-28T06:00:00.000Z"),
   "entries": [
      {
         "name": "Entry 1",
         "percentage": 28.9
      },
      {
         "name": "Entry 2",
         "percentage": 60.5
      },
      {
         "name": "Others",
         "percentage": 10.5
      }
   ]
 }

So the request I was try:

db.collection.aggregate([
{
    "$addFields": {
        "full_datetime": {"$substr": ["$time", 0, 19]}
    }
},        
{
   "$group": {
       "_id": "$full_datetime",
       "value_sum": {"$sum": "$value"},
       "entries": {
           "$push": {
               "name": "$name",
               "percentage": { 
                  "$multiply": [{ 
                      "$divide": ["$value", {"$literal": "$value_sum" }] 
                  }, 100 ]
               }
           }
       }
   }
}
])

This request is not work because $value_sum does not exists inside $push. Please help me how I can to send this $value_sum into the $push statement

RaR · Accepted Answer · 2018-02-04 15:41:13Z

You can use one more stage to calculate percentage using $map as,

db.collection.aggregate([
  "$addFields": {
    "full_datetime": {
      "$substr": ["$time", 0, 19]
    }
  }
}, {
  "$group": {
    "_id": "$full_datetime",
    "value_sum": {
      "$sum": "$value"
    },
    "entries": {
      "$push": {
        "name": "$name",
        "value": "$value"
      }
    }
  }
}, {
  "$project": {
    "entriesNew": {
      "$map": {
        "input": "$entries",
        "as": "entry",
        "in": {
          "name": "$$entry.name",
          "percentage": {
            "$multiply": [{
              "$divide": ["$$entry.value", "$value_sum"]
            }, 100]
          }
        }
      }
    }
  }
}])

Output:

/* 1 */
{
    "_id" : "2013-12-28T11:00:00",
    "entries" : [ 
        {
            "name" : "Entry 1",
            "percentage" : 28.9473684210526
        }, 
        {
            "name" : "Entry 2",
            "percentage" : 60.5263157894737
        }, 
        {
            "name" : "Entry 3",
            "percentage" : 6.57894736842105
        }, 
        {
            "name" : "Entry 4",
            "percentage" : 3.94736842105263
        }
    ]
}

/* 2 */
{
    "_id" : "2013-12-28T06:00:00",
    "entries" : [ 
        {
            "name" : "Entry 1",
            "percentage" : 33.3333333333333
        }, 
        {
            "name" : "Entry 2",
            "percentage" : 66.6666666666667
        }
    ]
}

Collectives™ on Stack Overflow

mongodb aggregation with array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related