Querying the total number of elements in nested arrays - embed documents MongoDB

Question

I have documents in my collections like to:

{
  _id: 1,
  activities: [
    {
      activity_id: 1,
      travel: [
        {
          point_id: 1,
          location: [-76.0,19.1]
        },
        {
          point_id: 2,
          location: [-77.0,19.3]
        }
      ]
    },
    {
      activity_id: 2,
      travel: [
        {
          point_id: 3,
          location: [-99.3,18.2]
        }
      ]
    }
  ]
},
{
  _id: 2,
  activities: [
    {
      activity_id: 3,
      travel: [
        {
          point_id: 4,
          location: [-75.0,11.1]
        }
      ]
    }
  ]
}

I can get the total number of activities, as follows:

db.mycollection.aggregate(
  {$unwind: "$activities"}, 
  {$project: {count:{$add:1}}}, 
  {$group: {_id: null, number: {$sum: "$count" }}}
)

I get (3 activities):

{ "result" : [ { "_id" : null, "number" : 3 } ], "ok" : 1 }

question: How can I get the total number of elements in all travels?

expected result: 4 elements

these are:

{
  point_id: 1,
  location: [-76.0,19.1]
},
{
  point_id: 2,
  location: [-77.0,19.3]
},
{
  point_id: 3,
  location: [-99.3,18.2]
},
{
  point_id: 4,
  location: [-75.0,11.1]
}

Actually this was and is still as simple as `db.mycollection.aggregate({ "$group": { "_id": null, "total": { "$sum": { "$sum": { "$map": { "input": "$activities", "as": "a", "in": { "$size": "$$a.travel" } } } } } }})`. This is because `$sum` could work directly with an array as well as being an accumulator since MongoDB 3.2. By your own deleted answer your MongoDB version at this time appears to be much older than that though. — Neil Lunn, Jun 29 '17 at 04:11

Saleem · Accepted Answer · 2016-06-08T20:58:43.923

You can easily transform document by using double $unwind

e.g.

db.collection.aggregate([
  {$unwind: "$activities"},
  {$unwind: "$activities.travel"},
  {$group:{
    _id:null, 
    travel: {$push: {
      point_id:"$activities.travel.point_id", 
      location:"$activities.travel.location"}}
  }},
  {$project:{_id:0, travel:"$travel"}}
])

This will emit which is very close to your desired output format:

{ 
    "travel" : [
        {
            "point_id" : 1.0, 
            "location" : [
                -76.0, 
                19.1
            ]
        }, 
        {
            "point_id" : 2.0, 
            "location" : [
                -77.0, 
                19.3
            ]
        }, 
        {
            "point_id" : 3.0, 
            "location" : [
                -99.3, 
                18.2
            ]
        }, 
        {
            "point_id" : 4.0, 
            "location" : [
                -75.0, 
                11.1
            ]
        }
    ]
}

Update:

If you just want to know total number of travel documents in whole collection,

try:

db.collection.aggregate([
  {$unwind: "$activities"},
  {$unwind: "$activities.travel"},
  {$group: {_id:0, total:{$sum:1}}}
])

It will print:

{ 
    "_id" : NumberInt(0), 
    "total" : NumberInt(4)
}

Update 2:

OP wants to filter documents based on some property in aggregation framework. Here is a way to do so:

db.collection.aggregate([
  {$unwind: "$activities"},
  {$match:{"activities.activity_id":1}},
  {$unwind: "$activities.travel"},
  {$group: {_id:0, total:{$sum:1}}}
])

It will print (based on sample document):

{ "_id" : 0, "total" : 2 }

is possible adding the parameter `{$match: { "activities.activity_id": 1}}` so I can get the list of travels of a specific activity ? .... how to do it ? — Jose Ricardo Bustos M., Jun 08 '16 at 20:35
yes, but ..... this does not work, see [Filtering embedded documents in MongoDB](http://stackoverflow.com/questions/2138454/filtering-embedded-documents-in-mongodb) .... I think I'll have to work with relational tables instead of embedded objects — Jose Ricardo Bustos M., Jun 08 '16 at 20:41
Well I believe it work. Right now I don't have access to machine but will update as soon as possible. — Saleem, Jun 08 '16 at 20:44
I see my mistake, the order of `$match` and `$unwind`parameters is critical — Jose Ricardo Bustos M., Jun 08 '16 at 21:05

Querying the total number of elements in nested arrays - embed documents MongoDB

1 Answers1