MongoDb aggregate not grouping correctly

user_14855782 0
Viewed 36

So what I want to do is get the total number of sport activities offered per province per type. For Berlin and Cologne the activities are Running and swimming The output should look something like this:

{ 
    "_id" : {
        "province" : "Berlin", 
        "type" : "Running"
    }, 
    "number" : 2.0
}
{ 
    "_id" : {
        "province" : "Berlin", 
        "type" : "Swimming"
    }, 
    " number" : 2.0
}
{ 
    "_id" : {
        "province" : "Cologne", 
        "type" : "Running"
    }, 
    "number" : 2.0
}
{ 
    "_id" : {
        "province" : "Cologne", 
        "type" : "Swimming"
    }, 
    "number" : 1.0
}

my query looks like this, im not getting the result that i want. It shows me the provinces where Running and Swimming are activities which are not Berlin or Cologne. I tried putting the $or insinde a $and and it still didnt produce the outcome i wanted. What am I missing?

db.getCollection("Sport").aggregate([
    {$match: {$or: [{province: "Cologne"}, {province: "Berlin"}], $or: [{type: "Running"}, {type: "Swimming"}]}},
    {$group: {_id: {province: "$province", type: "$type"}, count: {$sum: 1}}},
    {$sort: {count: -1}}
])
mongodb
2 Answers

So the issue is within the match query, specifically the query object has the same key more than once (the "$or" key), in this case the query parser of your driver takes the last appearance of the key, Here is a simplified example:

const query = { score: 1, score: 2 }
results = db.find(query)

With query the "results" will all have score: 2, because the score: 1 condition was overwritten when parsed by the query parser. Now replace score with $or and you get the point.

You can simplfy your query by using $in:

{
    "$match": {
        "province": {"$in": ["Cologne", "Berlin"]},
        "type": {"$in": ["Running", "Swimming"]}
    }
}

Or your instinct to use $and could also work, i'm assuming there was just some syntax issue there:

{
    $match: {
        $and: [
            {$or: [{province: "Cologne"}, {province: "Berlin"}]},
            {$or: [{type: "Running"}, {type: "Swimming"}]},
        ]
    },
}
user_107703700

Tom Slabbaert had already great input but having no idea how your data structure actually looks it's hard to pin point the mistake. But incase the field 'type' or 'province' is an array, you would need to unwind the field first.

  {
    $unwind: "$type"
  },
  {
    $match: {
      $and: [
        {
          $or: [
            {
              province: "Berlin"
            },
            {
              province: "Cologne"
            }
          ]
        },
        {
          $or: [
            {
              type: "Running"
            },
            {
              type: "Swimming"
            }
          ]
        }
      ]
    }
  }
user_132030390
Related