MongoDB query and calculating average for nested array elements

Question

I have a collection lanes which has documents of following structure. Only the dist and time of new documents changes. I want to calculate average-speeds of all lanes grouped by l_id.

{
    _id: 1213,
    ab:[
        {
            l_id: 101,
            dist: 100,
            time: 100
        },
        {
            l_id: 102,
            dist: 140,
            time: 120
        },
        {
            l_id: 103,
            dist: 10,
            time: 10
        }
    ]
}

like this :

{[
    {
        l_id: 101,
        avgspeed: 14
    },
    {
        l_id: 102,
        avgspeed: 19
    },
    {
        l_id: 103,
        avgspeed: 9
    }
]}

How can it be done using mongo aggregate/unwind query?

EDIT 1: (how to do it the structure is as follows)

{
    _id: 1213,
    ab:[
        {
            l_id: 101,
            data:[{
                dist: 100,
                time: 100
            }]
        },
        {
            l_id: 102,
            data:[{
                dist: 140,
                time: 120
            }]
        },
        {
            l_id: 103,
            data:[{
                dist: 10,
                time: 10
            }]
        }
    ]
}

something like this... avgspeed = doc[0](dist)/doc[0](time) + doc[1](dist)/doc[1](time) ... — atulj
– atulj, Commented Oct 16, 2016 at 14:32

Daniele Tassone · Accepted Answer · 2016-10-16 18:14:24Z

1

db.Test2.aggregate([
   {
     $unwind: "$ab"
   },
   {
     $unwind: "$ab.data"
   },

   {
     $group:{
       _id: "$ab.l_id",
       avgspeed: {$avg:{$divide:["$ab.data.dist","$ab.data.time"]}}
     }
   }
]);

edited Oct 16, 2016 at 18:14

answered Oct 16, 2016 at 14:27

Daniele Tassone

2,1842 gold badges18 silver badges26 bronze badges

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

2 Comments

atulj Over a year ago

How would we do it if the documents structure is like shown in edited question?

atulj Over a year ago

Hey Daniele, there's a small mistake. distance should be the first argument and time, second.

Collectives™ on Stack Overflow

MongoDB query and calculating average for nested array elements

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related