MongoDB Query using nested groups

Question

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?

the correct answer is 52 and 22, not 54 and 22.

Cristian Lupascu
– Cristian Lupascu

2013-09-15 12:09:00 +00:00
Commented Sep 15, 2013 at 12:09 — Cristian Lupascu
– Cristian Lupascu, Commented Sep 15, 2013 at 12:09

Yevgeniy Anfilofyev · Accepted Answer · 2013-09-15 11:39:25Z

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.

Cristian Lupascu · Accepted Answer · 2013-09-15 12:21:29Z

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

Collectives™ on Stack Overflow

MongoDB Query using nested groups

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related