CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Chris Lawton
Chris Lawton

Reputation: 47

MongoDB grouping and max item?

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:

  1. Return a count without actually getting the data.
  2. 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:

  1. The returned count: 4
  2. 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!!

Upvotes: 1

Views: 99

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

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.

Upvotes: 1

Related Questions