MongoDb aggregate not grouping correctly-CodePudding

So what I want to do is get the total number of sportactivities offered per province per type. For Berlin and Cologne the activites 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}}
])

CodePudding user response：

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