mongodb group by where 字段不等于 max

Question

我想获取所有(按 2 个字段分组)不等于最大值的文档。

每个文档都有多个版本，所以查看所有最新版本的查询是:

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

现在我需要这个查询的补充(所有以前的版本，不是最新的)。

我尝试了一些查询，如下所示，但都没有用。

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

编辑:输入/输出示例:输入:

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) }
])

因此，从 applicationId+name 获取最新版本的第一个查询的预期结果是:

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

我想要的输出基本上是这个查询的补充，它获取除了最新版本之外的所有结果(所有这些文档都有一个更新版本，对应于它们相应的 applicationId+name)

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

Answer 1

• $sort 按版本降序排列
• $group 通过 name 和 applicationId 构建所有版本数组
• $slice 从版本数组中删除最新版本
• $unwind 解构previousVersions数组

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

---

如果你正在寻找以前版本的原始文档，你可以试试，

• $sort 按版本降序排列
• $group 通过name 和applicationId 构建所有分组的文档
• $slice 从版本数组中删除最新版本
• $unwind 解构previousVersions数组
• $replaceRoot 将 previousVersions 对象替换为 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