MongoDB related table query

foreword

I recently learned to use mongodb to query the problem of using associated tables in the middle. I encountered some problems and recorded them to share with you.

mongodb associated query

I used SQL syntax to query the relationship between oracle, sqlserver, and mysql tables before, but after I got to mongodb, I couldn't start, and the writing method was completely different, so I went to the Internet to query the writing method of mongodb related table query, so I tried to write the reference code myself. , but found that there are many problems, such as I have two tables user and apple,

user table:

id	name	age
ObjectId(1xxxx1)	zhangsan	32
ObjectId(2xxxx2)	lishi	23
ObjectId(3xxxx3)	wangwu	19
ObjectId(4xxxx4)	liuliu	28

apple table:

id	uid	device
ObjectId(axxxxa)	1xxxx1	10
ObjectId(bxxxxb)	2xxxx2	2
ObjectId(cxxxxc)	3xxxx3	7
ObjectId(dxxxxd)	4xxxx4	1

user entity class:

@Setter
@Getter
public class User {
    private String id;
    private String name;
    private Integer age;
    private Date createDt;
}

apple entity class:

@Setter
@Getter
public class Apple {
    private String id;
    private String uid;
    private Integer device;
    private Date createDt;
}

There is a one-to-one relationship between user and apple, and now my query requirements are: query user data in descending order of the number of devices.

The query result should be:

id	name	age
ObjectId(1xxxx1)	zhangsan	32
ObjectId(3xxxx3)	wangwu	19
ObjectId(2xxxx2)	lishi	23
ObjectId(4xxxx4)	liuliu	28

We use the user table and the apple table as the main table to query, so let's try to write it using the mongodb syntax.

Use the user table as the main table query

// 1、添加_id字段类型转换
AddFieldsOperation addFieldsOperation = AddFieldsOperation.addField("_id").withValue(ConvertOperators.ToString.toString("$_id")).build();

// 2、按参数顺序：被关联表apple，主表user.id，被关联表apple.uid，别名
LookupOperation lookupOperation = Aggregation.lookup("apple", "_id", "uid", "apple_as");

// 3、查询哪些字段，类似sql里面的 select 选择器
ProjectionOperation project = Aggregation.project("name","age","createDt").and("apple_as.device").as("device");

// 4、按照apple.device数量排序
// 注意：不能使用apple_as.device，并且 device字段必须出现在project里面，否则查询失败
SortOperation sort = Aggregation.sort(Sort.Direction.DESC, "device");

// 5、添加取List中
List<AggregationOperation> operationList = Lists.newArrayList();
operationList.add(addFieldsOperation);
operationList.add(lookupOperation);
operationList.add(project);
operationList.add(sort);
Aggregation agg = Aggregation.newAggregation(operationList);

// 因为返回的是User实体类，所以不会出现device字段，你可以使用Map.class来返回想要的字段
AggregationResults<User> aggregationResults = mongoTemplate.aggregate(agg, "user", User.class);
// 6、返回关联查询结果
List<User> dataList = aggregationResults.getMappedResults();

Note: We need to convert the id of the main table to String type before querying, because the uid of the associated table is of String type, otherwise the query fails

Use apple table as main table query

// 1、添加uid字段类型转换
AddFieldsOperation addFieldsOperation = AddFieldsOperation.addField("uid").withValue(ConvertOperators.ToObjectId.toObjectId("$uid")).build();

// 2、按参数顺序：被关联表user，主表apple.uid，被关联表user.id，别名
LookupOperation lookupOperation = Aggregation.lookup("user", "uid", "_id", "user_as");

// 3、查询哪些字段，类似sql里面的 select 选择器
// 注意：因为user是被关联表，所以要使用user_as别名的方式来获取用户信息字段
ProjectionOperation project = Aggregation.project("user_as.name","user_as.age","user_as.createDt","device");

// 4、按照apple.device数量排序
SortOperation sort = Aggregation.sort(Sort.Direction.DESC, "device");

// 5、添加取List中
List<AggregationOperation> operationList = Lists.newArrayList();
operationList.add(addFieldsOperation);
operationList.add(lookupOperation);
operationList.add(project);
operationList.add(sort);
Aggregation agg = Aggregation.newAggregation(operationList);

// 因为返回的是User实体类，所以不会出现device字段，你可以使用Map.class来返回想要的字段
AggregationResults<User> aggregationResults = mongoTemplate.aggregate(agg, "apple", User.class);
// 6、返回关联查询结果
List<User> dataList = aggregationResults.getMappedResults();

Note: Before querying, we need to convert the uid of the main table to the ObjectId type, because the _id of the associated table is the ObjectId type, otherwise the query fails

Summarize

1. The mongodb associated query must pay attention to the conversion between the ObjectId type and the String type, otherwise it will be unsuccessful
2. The fields that appear in the project will affect the sort ordering
3. The order before and after the addition of operationList will also affect the query
4. If the query fails, I have to try it several times. I have also concluded the impact of which step occurs through continuous trial and error.
5. If possible, add the following code:

AggregationOperation match = Aggregation.match(Criteria.where("uid").is(uid));
LimitOperation limit = Aggregation.limit(10);

operationList.add(match);
operationList.add(limit);

Updated 2022-01-14

Three-table association

List<AggregationOperation> operationList = Lists.newArrayList();
AddFieldsOperation add1FieldsOperation = AddFieldsOperation.addField("categoryId").withValue(ConvertOperators.ToObjectId.toObjectId("$categoryId")).build();

LookupOperation lookupOperation1 = Aggregation.lookup("urlCategory", "categoryId", "_id", "category_as");
LookupOperation lookupOperation2 = Aggregation.lookup("user", "uid", "uid", "user_as");

MatchOperation match = Aggregation.match(Criteria.where("uid").in(uids).and("isDelete").is(false));
ProjectionOperation project = Aggregation.project("user_as.userName","user_as.avatar","uid", "title", "url", "domain", "favicon", "isAttention", "isCopy", "createdDt")
        .and("category_as.name").as("categoryName")
        .andExpression("{ $dateToString: {date: '$createdDt', format: '%Y-%m-%d %H:%M:%S', timezone: '+08:00'}}").as("date")
        .and(ConvertOperators.Convert.convertValueOf("categoryId").to("string")).as("category_id");
// sort里面的clickCount字段必须从Project中取，否则 失败
SortOperation sort = Aggregation.sort(Sort.Direction.DESC, "createdDt");
FacetOperation facet = Aggregation.facet(
        count().as("count")).as("total")
        .and(skip(pageSize * (pageNum - 1)), limit(pageSize)).as("rows");
UnwindOperation unwindOperation1 = Aggregation.unwind("category_as");
UnwindOperation unwindOperation2 = Aggregation.unwind("user_as");
operationList.add(add1FieldsOperation);
operationList.add(lookupOperation1);
operationList.add(lookupOperation2);
operationList.add(unwindOperation1);
operationList.add(unwindOperation2);
//因为match里面含有url_as，所以lookupOperation要在match上面
operationList.add(match);
operationList.add(project);
operationList.add(sort);
operationList.add(facet);

Aggregation agg = Aggregation.newAggregation(operationList);
// collectionName:主表
AggregationResults<HashMap> aggregationResults = mongoTemplate.aggregate(agg, "url", HashMap.class);
return aggregationResults.getMappedResults();