mongo 两个字段相等查询
软件编程
349
皇太极
: 2021-04-23 14:54:19

mongo 两个字段相等查询

Mongo 版本

Mongodb-Server:3.6.4
mongodb-driver:3.8.2
spring-boot-starter-data-mongodb:2.1.6.RELEASE

先看下marion_0423表结构
  1. {
  2. "_id" : ObjectId("60822ae77dc4ee0cc8b17420"),
  3. "unionId" : "abc",
  4. "customerId" : "abc",
  5. "cnt" : 2.0
  6. }
  7. // ----------------------------------------------
  8. {
  9. "_id" : ObjectId("60822af57dc4ee0cc8b17421"),
  10. "unionId" : "def",
  11. "customerId" : "def333"
  12. }

如上述表结构,我们现在需要查询 unionId==customerId 的数据,及做分组等操作。

1. 简单查询
  1. -- 判断俩字段相等:
  2. db.marion_0423.find({"$where":"this.unionId == this.customerId "})
  3. -- 判断不相等:
  4. db.marion_0423.find({"$where":"this.unionId != this.customerId "})
  1. Criteria criteria = new Criteria();
  2. criteria.and("$where").is("this.unionId==this.customerId");
2. 复杂查询 Aggregation
2.1 method 1
  1. db.getCollection("marion_0423").aggregate(
  2. [
  3. {
  4. "$match" : {
  5. "$expr":{"$eq":["$unionId", "$customerId"]}
  6. }
  7. }
  8. ]
  9. );
  1. // 拼接条件
  2. Map<String,Object> expr = new HashMap<>();
  3. expr.put("$eq",Arrays.asList("$unionId","$customerId"));
  4. Criteria criteria = Criteria.where("$expr").is(expr);
  5. // 聚合操作
  6. List<AggregationOperation> operations = new ArrayList<>();
  7. // 筛选条件
  8. operations.add(Aggregation.match(criteria));
  9. Aggregation aggregation = Aggregation.newAggregation(operations);
2.2 method 2
  1. db.getCollection("marion_0423").aggregate(
  2. [
  3. {
  4. "$project" : {
  5. "unionId" : 1.0,
  6. "customerId" : 1.0,
  7. "difference" : { "$eq" : [ "$unionId", "$customerId" ] }
  8. }
  9. },
  10. {
  11. "$match" : { "difference":true }
  12. }
  13. ]
  14. );
  1. // 聚合操作
  2. List<AggregationOperation> operations = new ArrayList<>();
  3. operations.add(Aggregation.project("unionId","customerId").and(AggregationSpELExpression.expressionOf("eq(unionId,customerId)")).as("difference"));
  4. operations.add(Aggregation.match(Criteria.where("difference").is(true)));
  5. Aggregation aggregation = Aggregation.newAggregation(operations);