CODEWITHSUNDEEP
mongodbaggregation-framework
user962206
user962206

Reputation: 16147

MongoDB Query using nested groups

With this given data.

{ "_id" : 0, "a" : 0, "b" : 0, "c" : 21 }
{ "_id" : 1, "a" : 0, "b" : 0, "c" : 54 }
{ "_id" : 2, "a" : 0, "b" : 1, "c" : 52 }
{ "_id" : 3, "a" : 0, "b" : 1, "c" : 17 }
{ "_id" : 4, "a" : 1, "b" : 0, "c" : 22 }
{ "_id" : 5, "a" : 1, "b" : 0, "c" : 5 }
{ "_id" : 6, "a" : 1, "b" : 1, "c" : 87 }
{ "_id" : 7, "a" : 1, "b" : 1, "c" : 97 }

The query is this

db.fun.aggregate([{
    $group: {
        _id: { a: "$a", b: "$b" },
        c: { $max: "$c" }
    }
}, {
    $group: {
        _id: "$_id.a",
        c: { $min: "$c" }
    }
}])

The correct answer is 

54 and 22

How come is it like that? I was expecting to be 97 and 21

Why have I thought of 97 as max and 21 as min? becaus all of the documents that has a similar values(namely a and b) c is the highest and the lowest is 21

can someone eloborate this to me? and how $group really work?

Upvotes: 1

Views: 168

Answers (2)

Cristian Lupascu
Cristian Lupascu

Reputation: 40576

This problem is really easy to look at from a relational (SQL) perspective, because $group in your scenario is very similar to SQL's GROUP BY.

So, assuming your data was in a table called MongoData with columns A, B and C, this is how the select equivalent to your aggregation would look like:

select A, min(C) MinC
from (
    select A, max(C)
    from MongoData
    group by A, B
  ) X (A, C)
group by A;

The results after running step 1 of the aggregation (or the inner Select) are:

A   B   MaxC
------------
0   0   54
1   0   22
0   1   52
1   1   97

Step2 reduces this data by grouping only by A to:

A   MinC
--------
0   52
1   22

Take a look at this SQLFiddle to see how the data looks after each of the two steps: http://sqlfiddle.com/#!3/7d4f5/10

Upvotes: 0

Yevgeniy Anfilofyev
Yevgeniy Anfilofyev

Reputation: 4847

First $group gives you result of grouping by a and b and selecting max value of c. So, for example, it gets a=0 and b=0 combinations:

{ "_id" : 0, "a" : 0, "b" : 0, "c" : 21 }
{ "_id" : 1, "a" : 0, "b" : 0, "c" : 54 }

and for this it will group them and select max of 21 or 54, which is 54. So you get this result for all groups:

    {
        "_id" : {
            "a" : 1,
            "b" : 1
        },
        "c" : 97
    },
    {
        "_id" : {
            "a" : 1,
            "b" : 0
        },
        "c" : 22
    },
    {
        "_id" : {
            "a" : 0,
            "b" : 1
        },
        "c" : 52
    },
    {
        "_id" : {
            "a" : 0,
            "b" : 0
        },
        "c" : 54
    }

This is what your second $group will get to work with. And at this time it groups by a and selects min of c. So you can see that for a=1 min of c (from 22 and 97) will be 22 and for a=0 min c (from 52 and 54) will be 52.

So it is how you get 22 and 52.

Upvotes: 1

Related Questions