如何基于多条件在MongoDB中过滤排除指定文档？

原始文档集合 #

-> {"type" : "WIRE",  "subtype" : "RETURN", "amount" : 3500, "gid" : 2975075},
{"type" : "FEE", "subtype" : "INCOME", "amount" : 1750, "gid" : 2975075},
{"type" : "FEE", "subtype" : "TXN", "amount" : 1750, "gid" : 2975075},
-> {"type" : "WIRE", "subtype" : "TXN", "amount" : 3500,"gid" : 2975075},
{"type" : "FEE", "subtype" : "INCOME", "amount" : 0.08, "gid" : 2895112},
{"type" : "FEE", "subtype" : "TXN", "amount" : 0.08, "gid" : 2895112},
{"type" : "WIRE", "subtype" : "TXN", "amount" : 5, "gid" : 2895112}

问题分析 #

你需要排除的是两条带->标记的特定记录：

1. {"type" : "WIRE", "subtype" : "RETURN", "amount" : 3500, "gid" : 2975075}
2. {"type" : "WIRE", "subtype" : "TXN", "amount" : 3500,"gid" : 2975075}

你之前的查询条件逻辑错误：db.collection.find({$and:[{type:{$ne:"WIRE"}},{subtype:{$nin:["RETURN","TXN"]}},{gid:{$ne:2975075}}]}要求同时满足「type不是WIRE」「subtype不在RETURN/TXN里」「gid不是2975075」，这会把符合要求的WIRE类型记录（gid=2895112）和gid=2975075的FEE记录都过滤掉，不符合预期。

正确查询语句 #

使用$nor操作符直接排除这两条特定记录：

db.collection.find({
  $nor: [
    { type: "WIRE", subtype: "RETURN", amount: 3500, gid: 2975075 },
    { type: "WIRE", subtype: "TXN", amount: 3500, gid: 2975075 }
  ]
})

或者用$not结合$or实现同样效果：

db.collection.find({
  $not: {
    $or: [
      { type: "WIRE", subtype: "RETURN", amount: 3500, gid: 2975075 },
      { type: "WIRE", subtype: "TXN", amount: 3500, gid: 2975075 }
    ]
  }
})

期望查询结果 #

{"type" : "FEE", "subtype" : "INCOME", "amount" : 1750, "gid" : 2975075},
{"type" : "FEE", "subtype" : "TXN", "amount" : 1750, "gid" : 2975075},
{"type" : "FEE", "subtype" : "INCOME", "amount" : 0.08, "gid" : 2895112},
{"type" : "FEE", "subtype" : "TXN", "amount" : 0.08, "gid" : 2895112},
{"type" : "WIRE", "subtype" : "TXN", "amount" : 5, "gid" : 2895112}

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