MongoDb连接表的查询

短命女 2022-10-14 15:52 4阅读 0赞

### 一、外键关联 ###

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();