分组查询：筛选同一客户同一协议类型下值不同的记录

我们需要从给定的客户协议数据中，筛选出同一agreementType下存在不同agreementValue的客户（比如示例中的Smith，其agreementType=A有T和F两种值）。

示例数据 #

{
    "_id": "123",
    "customerID" : "Smith",
    "agreement": [
        {"agreementType":"A", "agreementValue":"T"},
        {"agreementType":"B", "agreementValue":"T"}
    ]
}
{
    "_id": "124",
    "customerID" : "Smith",
    "agreement": [
        {"agreementType":"A", "agreementValue":"F"},
        {"agreementType":"B", "agreementValue":"T"}
    ]
}
{
    "_id": "125",
    "customerID" : "Brown",
    "agreement": [
        {"agreementType":"A", "agreementValue":"T"},
        {"agreementType":"B", "agreementValue":"T"}
    ]
}
{
    "_id": "126",
    "customerID" : "Brown",
    "agreement": [
        {"agreementType":"A", "agreementValue":"T"},
        {"agreementType":"B", "agreementValue":"T"}
    ]
}

解决方案：使用MongoDB聚合管道 #

通过以下聚合步骤实现需求：

• 拆分嵌套的agreement数组，将每个协议对象转为单独文档
• 按客户ID和协议类型分组，收集每种组合下的所有不同值
• 筛选出值数量大于1的分组，这类分组对应的客户即为目标客户
• 去重后提取最终客户列表

对应的聚合查询代码：

db.collection.aggregate([
  { $unwind: "$agreement" },
  {
    $group: {
      _id: {
        customerID: "$customerID",
        agreementType: "$agreement.agreementType"
      },
      distinctValues: { $addToSet: "$agreement.agreementValue" }
    }
  },
  { $match: { $expr: { $gt: [{ $size: "$distinctValues" }, 1] } } },
  { $group: { _id: "$_id.customerID" } },
  { $project: { _id: 0, customerID: "$_id" } }
])

查询结果 #

执行后会返回符合条件的客户：

{ "customerID" : "Smith" }

内容的提问来源于stack exchange，提问作者asver