I have a mongoDB database, called trips with the following structure:

{'Name': 'Joe Doe',
 'WentTo' : 
   [{ 'Destination':
      { 'City': 'Tirana',
        'Country': 'Albania'}},
    { 'Destination':
      { 'City': 'Bari',
        'Country': 'Italy'}},
    { 'Destination':
      { 'City': 'Pisa',
        'Country': 'Italy'}} }] }
{'Name': 'Jane Doe',
 'WentTo' : 
   [{ 'Destination':
      { 'City': 'Perth',
        'Country': 'Australia'}},
    { 'Destination':
      { 'City': 'Bern',
        'Country': 'Switzerland'}},
    { 'Destination':
      { 'City': 'Rome',
        'Country': 'Italy'}} }] }   

I would like to list the travelers that have been to Italy and the number of times they have been there, like this:

{ "Name" : "Joe Doe", "Times in Italy" : 2 }
{ "Name" : "Jane Doe", "Times in Italy" : 1 }

I came up with this approach but MongoDB doesn't output anything.

db.trips.aggregate([ {$unwind:'$WentTo.Destination'}, 
{$match: {'Country':'Italy'}}, {$group:{_id:'$Name', Times in Italy:{$sum:1}}}])

Any ideas?

CodePudding user response：

Maybe something like this:

Option 1: $filter/$size (Faster and effective when there is no duplicate records with same name )

db.collection.aggregate([
{
"$addFields": {
  "WentTo": {
    $size: {
      "$filter": {
        "input": "$WentTo",
        "as": "w",
        "cond": {
          "$eq": [
            "$$w.Destination.Country",
            "Italy"
          ]
        }
       }
     }
   }
  }
},
{
   $project: {
     "Times in Italy": "$WentTo",
     Name:1
   }
 }
])

Explained:

1. Use addFields with $filter to match only array elements having Italy as Country and count them with $size
2. Project the "WentTo" array as "Times in Italy" and Name as per requested.

Playground 1

Option 2: This is your query with small corrections that is also covering the case when there is dulicated records per name ,please, note for bigger collections $unwind operation can be performance impacting and slow ...

 db.collection.aggregate([
 {
   $unwind: "$WentTo"
 },
 {
  $match: {
    "WentTo.Destination.Country": "Italy"
  }
 },
 {
  $group: {
    _id: "$Name",
      "Times in Italy": {
        $sum: 1
    }
   }
  }
 ])

Playground 2