Question 116966 in Mongodb Question posted in
The official documentation can be found here.

MongoDb aggregate not grouping correctly

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}}
])
Tags:

2

Answers


  1. 0

    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"}]},
        ]
    },
}
    Login or Signup to reply.
  2. 0

    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"
            }
          ]
        }
      ]
    }
  }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search