MongoDb连接表的查询

一、外键关联

a_table关联b_table通过a_table的a_field=b_table的b_field，在a_table生成lookup_field字段存储关联的b_table数据

MongoOperations mongoOperations = ...;
        
LookupOperation agg = Aggregation.lookup("b_table", "a_field",
                "b_field", "lookup_field");
Aggregation aggregation = Aggregation.newAggregation(agg);
List<Map> list = mongoOperations.aggregate(aggregation, "a_table", Map.class).getMappedResults();

二、内连接

a_table关联b_table通过a_table的a_field=b_table的b_field，在a_table生成lookup_field字段存储关联的b_table数据，unwind lookup_field字段

MongoOperations mongoOperations = ...;

LookupOperation agg = Aggregation.lookup("b_table", "a_field",
                "b_field", "lookup_field");
        
UnwindOperation unwindOperation = Aggregation.unwind("lookup_field");

Aggregation aggregation = Aggregation.newAggregation(agg, unwindOperation);
List<Map> list = mongoOperations.aggregate(aggregation, "a_table", Map.class).getMappedResults();

 

三、左连接

a_table关联b_table通过a_table的a_field=b_table的b_field，在a_table生成lookup_field字段存储关联的b_table数据，unwind lookup_field字段，保留null或空数组的数据

MongoOperations mongoOperations = ...;

LookupOperation agg = Aggregation.lookup("b_table", "a_field",
                "b_field", "lookup_field");

UnwindOperation unwindOperation = Aggregation.unwind("lookup_field", true);
       
Aggregation aggregation = Aggregation.newAggregation(agg, unwindOperation);
List<Map> list = mongoOperations.aggregate(aggregation, "a_table", Map.class).getMappedResults();

 

四·、左连接、提取右表第一条数据

a_table关联b_table通过a_table的a_field=b_table的b_field，在a_table生成lookup_field字段存储关联的b_table数据, unwind lookup_field字段，保留null或空数组的数据，提取第一条数据

 MongoOperations mongoOperations = ...;

LookupOperation agg = Aggregation.lookup("b_table", "a_field",
                "b_field", "lookup_field");

String arrayIndex = "index";
UnwindOperation unwindOperation = Aggregation.unwind("lookup_field", arrayIndex, true);
        
MatchOperation matchOperation = Aggregation.match(new Criteria()
                .orOperator(Criteria.where(arrayIndex).is(0), Criteria.where(arrayIndex).is(null)));
        
ProjectionOperation projectionOperation = Aggregation.project().andExclude(arrayIndex);
       
Aggregation aggregation = Aggregation.newAggregation(agg, unwindOperation, matchOperation, projectionOperation);
List<Map> list = mongoOperations.aggregate(aggregation, "a_table", Map.class).getMappedResults();