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]
}

Answer 1

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 }