mongodb group by where field not equals to max

user_7252805 0
Viewed 213

I want to get all documents that (grouped by 2 fields) are not equal to their max.

There are multiple versions for every document, so the query to view all the latest versions is:

db.getCollection('datatype').aggregate(
   [
     {
       $group:
         {
           _id: {
               "name": "$name",
               "applicationId": "$applicationId"
               },
           latest: { $max: "$version" }
         }
     }
   ]
)

Now I need the complement of this query (all previous versions, not the latest).

I tried some queries, like the below, but none worked.

db.getCollection('datatype').aggregate(
   [
     {
       $group:
         {
           _id: {
               "name": "$name",
               "applicationId": "$applicationId"
               },
           previousVersions: 
               { $ne: {$max: "$version"} }
               
         }
     }
   ]
)

EDIT: input/output examples: input:

db.getCollection('datatype').insertMany([
   { applicationId: "AAAA", name: "foo", version: NumberInt(1) },
   { applicationId: "AAAA", name: "foo", version: NumberInt(2) },
   { applicationId: "AAAA", name: "foo", version: NumberInt(3) },
   { applicationId: "BBBB", name: "foo", version: NumberInt(1) },
   { applicationId: "CCCC", name: "bar", version: NumberInt(1) },
   { applicationId: "CCCC", name: "bar", version: NumberInt(2) }
])

So the expected results from the first query that takes the latest version from applicationId+name, is:

{ applicationId: "AAAA", name: "foo", version: NumberInt(3) },
{ applicationId: "BBBB", name: "foo", version: NumberInt(1) },
{ applicationId: "CCCC", name: "bar", version: NumberInt(2) }

And my desired output is basically the complement of this query, that takes all the results besides the latest version (all these documents has an updated version, for their corresponding applicationId+name)

{ applicationId: "AAAA", name: "foo", version: NumberInt(1) },
{ applicationId: "AAAA", name: "foo", version: NumberInt(2) },
{ applicationId: "CCCC", name: "bar", version: NumberInt(1) }
aggregation-framework mongodb-query mongodb
2 Answers

There is no single command to do this, if the natural order of the documents in the db matches the version order and you're not using any specific indexes on other fields who might mix the order up you could use $slice to remove the unwanted version, like so:

db.collection.aggregate([
  {
    $group: {
      _id: {
        "name": "$name",
        "applicationId": "$applicationId"
      },
      not_latest: {
        "$push": "$version"
      }
    }
  },
  {
    $project: {
      _id: 1,
      not_latest: {
        $slice: [
          "$not_latest",
          0,
          {
            $subtract: [
              {
                $size: "$not_latest"
              },
              1
            ]
          }
        ]
      }
    }
  }
])

Mongo Playground

If for some reason you can't rely on the order just add a $sort stage with version: 1 prior to the $group stage and use the same code.

And if for some reason you don't want to $sort you could temporarily save the latest and use it to filter out the final results, like so:

db.collection.aggregate([
  {
    $group: {
      _id: {
        "name": "$name",
        "applicationId": "$applicationId"
      },
      not_latest: {
        "$push": "$version"
      },
      latest: {
        $max: "$version"
      }
    }
  },
  {
    $project: {
      _id: 1,
      not_latest: {
        $filter: {
          input: "$not_latest",
          as: "elem",
          cond: {
            $ne: [
              "$$elem",
              "$latest"
            ]
          }
        }
      }
    }
  }
])

Mongo Playground

user_107703700
  • $sort order by version in descending order
  • $group by name and applicationId and construct the all versions array
  • $slice to remove latest version from versions array
  • $unwind to deconstruct the previousVersions array
db.collection.aggregate([
  { $sort: { version: -1 } },
  {
    $group: {
      _id: {
        "name": "$name",
        "applicationId": "$applicationId"
      },
      previousVersions: { $push: "$version" }
    }
  },
  {
    $project: {
      _id: 0,
      applicationId: "$_id.applicationId",
      name: "$_id.name",
      previousVersions: {
        $slice: ["$previousVersions", 1, { $size: "$previousVersions" }]
      }
    }
  },
  { $unwind: "$previousVersions" }
])

Playground

If you are looking for original document of previous version you can try,

  • $sort order by version in descending order
  • $group by name and applicationId and construct the all grouped documents
  • $slice to remove latest version from versions array
  • $unwind to deconstruct the previousVersions array
  • $replaceRoot to replace previousVersions object to root
db.collection.aggregate([
  { $sort: { version: -1 } },
  {
    $group: {
      _id: {
        "name": "$name",
        "applicationId": "$applicationId"
      },
      previousVersions: { $push: "$$ROOT" }
    }
  },
  {
    $project: {
      _id: 0,
      previousVersions: {
        $slice: ["$previousVersions", 1, { $size: "$previousVersions" }]
      }
    }
  },
  { $unwind: "$previousVersions" },
  { $replaceRoot: { newRoot: "$previousVersions" } }
])

Playground

user_89871280
Related