Selecting documents with largest value of a field

[Luis_de_la_Torre]

I have a collection c of documents that look like this:

{"name": "A", "version": 1, "cost": 5, "value": 3},
{"name": "A", "version": 2, "cost": 10, "value": 2},
{"name": "B", "version": 1, "cost": 3, "value": 5},
{"name": "B", "version": 3, "cost": 7, "value": 2}

Across all documents, name + version is a unique identifier (e.g., there is only one document with name A and version 1).

For each name, I want to select the document with the largest version. In this case,

{"name": "A", "version": 2, "cost": 10, "value": 2},
{"name": "B", "version": 3, "cost": 7, "value": 2}

I can group to get the largest version for each name,

db.c.aggregate([
  {
    $group: {
      _id: {"name": "$name"},
      "max(c_version)": {$max: "$version"}
    }
  },
  {
    $project: {"name": "$_id.name", "version": "$max(c_version)", "_id": 0}
  }
])

but then of course I lose information on the other columns.

In SQL, I’d use a subquery or CTE, but this doesn’t seem possible with mongo:

with max_version as (
    select
        name,
        max(version) as version
    from c
    group by
        name
)
select
    c.cost, c.name, c.value, c.version
from c join max_version
on c.name = max_version.name
and c.version = max_version.version;

[Prasad_Saya]

Hello @Luis_de_la_Torre, welcome to the MongoDB Community forum!

You can try this approach:

db.test.aggregate([
  { $sort: { name: 1, version: -1 } },
  { $group: { _id: "$name", doc_with_max_ver: { $first: "$$ROOT" } } },
  { $relaceWith: "$doc_with_max_ver" }
])

[Luis_de_la_Torre]

Thank you! There is a typo (relace), but this is exactly what I needed.

[Prasad_Saya]

@Prasad_Saya

{ $relaceWith: "$doc_with_max_ver" }

@Luis_de_la_Torre, you are correct. It should be { $replaceWith: ...