How to get multiple average values from matching elements in array

Question

I have collection like below:

[{
    date: '20170721',
    pageUrl: 'page1',
    timing: [{ name: 'dns', duration: 1000 }, { name: 'tcp', duration: 2000 }]
}, {
    date: '20170721',
    pageUrl: 'page2',
    timing: [{ name: 'dns', duration: 1001 }, { name: 'tcp', duration: 1800 }]
}, {
    date: '20170722',
    pageUrl: 'page1',
    timing: [{ name: 'dns', duration: 1021 }, { name: 'tcp', duration: 1700 }]
}, {
    date: '20170722',
    pageUrl: 'page2',
    timing: [{ name: 'dns', duration: 1101 }, { name: 'tcp', duration: 1850 }]
}]

and I want the result of average timing of a given page during a given period of date.

For example: I need average timing data of page1, from date 20170701 - 20170731

And the expected output should like:

[{
    _id: '20170701',
    dns: <avgDuration>,
    tcp: <avgDuration>
}, {
    _id: '20170702',
    dns: <avgDuration>,
    tcp: <avgDuration>
},
...
]

what I tried is, and it didn't work:

db.myCollection.aggregate([
    { $match: { 'pageUrl': targetPageUrl } },
    { $group: {
        _id: '$date',
        dns: { $avg: '$timing.0.duration' },
        tcp: { $avg: '$timing.1.duration' }
    },
    ...
])

Anybody could help? Please

Neil Lunn · Accepted Answer · 2017-07-31 06:18:42Z

1

If the positions are always "fixed" then you can use $arrayElemAt:

db.myCollection.aggregate([
    { '$match': { 'pageUrl': targetPageUrl } },
    { '$group': {
        _id: '$date',
        dns: { '$avg': { '$arrayElemAt': [ '$timing.duration', 0 ] }  },
        tcp: { '$avg': { '$arrayElemAt': [ '$timing.duration', 1 ] } }
    }}
])

If they are not actually fixed, then use $filter to get the matching values:

db.myCollection.aggregate([
    { '$match': { 'pageUrl': targetPageUrl } },
    { '$group': {
        _id: '$date',
        dns: { 
          '$avg': {
            '$avg': {
              '$map': {
                'input': { 
                 '$filter': {
                   'input': '$timing',
                   'as': 't',
                   'cond': { '$eq': [ '$$t.name', 'dns' ] }
                 },
                 'as': 't',
                 'in': '$$t.duration'
               } 
            }
          }
        },
        tcp: { 
          '$avg': { 
            '$avg': {
              '$map': {
                'input': { 
                 '$filter': {
                   'input': '$timing',
                   'as': 't',
                   'cond': { '$eq': [ '$$t.name', 'tcp' ] }
                 },
                 'as': 't',
                 'in': '$$t.duration'
               }
            }
          }
        }
    }}
])

Being that with $filter it actually is possible to have "multiple matches" in an array and "reduce" them down, using $avg in the mode as "both" an accumulator and something that takes an "array" as an argument itself. Hence the "double" usage of $avg.

Or even using $indexOfArray if you feel you really must:

db.myCollection.aggregate([
    { '$match': { 'pageUrl': targetPageUrl } },
    { '$group': {
        _id: '$date',
        dns: { 
          '$avg': {
            '$arrayElemAt': [
              '$timing.duration',
              { '$indexOfArray': [ '$timing.name', 'dns' ] }
            ]
          }
        },
        tcp: { 
          '$avg': {
            '$arrayElemAt': [
              '$timing.duration',
              { '$indexOfArray': [ '$timing.name', 'tcp' ] }
            ]
          }
        }
    }}
])

edited Jul 31, 2017 at 6:18

answered Jul 31, 2017 at 6:10

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ygjack Over a year ago

Thanks very much. and may I know why $timing.0.duration is not support here?

Neil Lunn Over a year ago

@ygjack It never has been. Whilst that form of "dot notation" using an index value is valid for "query" and "projection" it was never valid for the aggregation framework. Only since MongoDB 3.2 has it actually been valid to get an "array" in response to something like '$timing.duration', and at that time of introduction $arrayElemAt was also added

ygjack Over a year ago

Thanks for the explaination

Collectives™ on Stack Overflow

How to get multiple average values from matching elements in array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related