Mongo Aggregate issue

Question

Here is my data in mongodb

{
    "_id" : ObjectId("5a77e82c19e5b90363fe55d4"),
    "serviceId" : 85,
    "clusterId" : 122,
    "metricTimestamp" : ISODate("2018-02-05T04:33:58.000Z"),
    "metricNames" : [ 
        "host", 
        "count", 
        "time", 
        "out"
    ],
    "metricValues" : [ 
        "wwe.123.com", 
        "8829", 
        "2018-02-05T04:16:02.685Z", 
        "25327782"
    ],
    "createtime" : ISODate("2018-02-05T05:14:20.273Z")
}

and I want to aggregate the data:

db.getCollection('metrics.value').aggregate([
    { $match: {
        'serviceId': 85, 
        'metricTimestamp':{ 
            $gte: ISODate("2018-02-03T04:33:58"),
            $lte: ISODate("2018-02-05T04:33:58")
        }
    }},
    { $project: {
        host: { $arrayElemAt:["$metricValues", 0]}, 
        count:{ $arrayElemAt:["$metricValues", 1]}
    },
    {$group:{}}
}])

I want to group by host (here it is "wwe.123.com"), and aggregate the count (here it is "8829"). I don't know how to convert from string to int (parseInt is not working). Please help me solve these issues.

I'm not sure what you need, as far you want to group by host and also count? or you want to do something with "count" like summarizing this because why you need "count" to be an integer. Could you please write a valid output? — Raul Rueda, Feb 05 '18 at 18:32
@user5438747 please see my answer in response to the `group` — , Feb 06 '18 at 09:15

score 0 · Answer 1 · answered Feb 05 '18 at 19:59

db.getCollection('foo').aggregate([
    { $match: 
      { "serviceId" : 85, 
        "metricTimestamp" : 
         {  $gte: ISODate("2018-02-05T04:33:58.000Z"), 
            $lte: ISODate("2018-02-05T04:33:58.000Z") }}},
     { $project :  
       { "_id" : 0, 
         "host" : { $arrayElemAt:["$metricValues", 0] }, 
         "count" : { $arrayElemAt:["$metricValues", 1] }}},

    ])

Outputs:

{
    "host" : "wwe.123.com",
    "count" : "8829"
}

In order to then $group after your $projection you would need to convert count field into an int. How you would do that has already been asked and answered here. once you've converted your field to an int you could just add this snippet after your $projection

{ $group : { _id : "$host", count : { $sum : "$count"}}}

Mongo Aggregate issue

1 Answers1