MongoDB grouping and max item?

Question

For the life of me, I can't figure out how to write this query in MongoDB!

I need to return rows from a query where the max "Revision" is the row returned for an item

I have two parts:

Return a count without actually getting the data.
Return all the data (not necessarily in order by column A but would be helpful) based on the grouping.

Here's a sample set (In our real world data, we have ~40 columns and potentially millions of rows):

    A       B       C       D       Revision
    --------+-------+-------+-------+------------
    Item1   100     200     300     1
    Item1   111     222     333     2
    Item2   200     500     800     1
    Item2   222     555     888     2
    Item2   223     556     889     3
    Item3   300     600     900     1
    Item4   400     700     1000    1

What I need to be returned:

The returned count: 4

The returned data:

A       B       C       D       Revision
--------+-------+-------+-------+--------
Item1   111     222     333     2
Item2   223     556     889     3
Item3   300     600     900     1
Item4   400     700     1000    1

I've been trying so many combination or $group and $count and even tried to use SQL to MongoDB query translation tools but can't seem to make it work.

Please help and thanks in advance!!

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-04-18 00:45:13Z

1

Try below query :

db.collection.aggregate([
  {
    $group: {
      _id: "$A",
      A: { $first: "$A" },
      B: { $max: "$B" },
      C: { $max: "$C" },
      D: { $max: "$D" },
      Revision: { $max: "$Revision" },
    }
  },
  {
    $project: { _id: 0 },
  },
  {
    $group: {
      _id: "",
      count: { $sum: 1 },
      data: { $push: "$$ROOT" }
    }
  }
]);

Test : mongoplayground

So in the data field you'll have actual documents where as count represent no.of unique 'A''s in collection.

In other way if you don't want result to be in that way you can just try below query :

db.collection.aggregate([
  {
    $group: {
      _id: "$A",
      A: { $first: "$A" },
      B: { $max: "$B" },
      C: { $max: "$C" },
      D: { $max: "$D" },
      Revision: { $max: "$Revision" },
    }
  },
  {
    $project: { _id: 0 },
  }
])

Test : mongoplayground

It would return an array of objects cause aggregation will always return a cursor which will be resolved to an array in code then you can just do .length on array to get total count instead of using second $group stage in first query to get just the total count this is another option to do.

edited Apr 18, 2020 at 0:45

answered Apr 18, 2020 at 0:39

whoami - fakeFaceTrueSoul

18.1k6 gold badges35 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Lawton Over a year ago

Thanks. The queries work. With your guidance I think I can move forward.

Collectives™ on Stack Overflow

MongoDB grouping and max item?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related