Before because of
There is an interface about information statistics, need to implement similar mysql multi-table associated query in MongoDB, how can MongoDB like Hibernate one-to-one, one-to-many mapping relationship? This section uses a combination of loopUp and unwind to do this
demand
-
Query the medal task name and related configuration information in the medal task main table
-
Export the file result comparison information of each person’s task
Note: The documents Medal, MedalTask and MedalTaskFile are involved. The documents have a one-to-many relationship from left to right
implementation
Bad implementation ideas
Implementation approach
-
Select MedalId and taskIds from MedalTask
-
Query data sets from Medal and MedalTaskFile for secondary aggregation
Aggregation Advanced lookup
First post code
/* Data aggregation */
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.match(criteria),
/* Associated medal */
Aggregation.lookup("medal"."medalId"."_id"."medal"),
/* Associate tasks */
Aggregation.lookup("medalTaskFile"."_id"."medalTaskId"."taskFiles"),
/* Query the start value */
Aggregation.skip(params.getPageNum() > 1 ? (params.getPageNum() - 1) * params.getPageSize() : 0),
/* Paging size */
Aggregation.limit(params.getPageSize()),
/ * * /
Aggregation.sort(Sort.by(Sort.Order.desc("createdTime"))),
The Medal * / / * eggs
Aggregation.unwind("medal")); List<MedalUserTo> medalUserTos = medalV4MongoTemplate.aggregate(aggregation, MedalTask.class, MedalUserTo.class).getMappedResults();Copy the code
@Data
private class MedalUserTo {
private String medalId;
private String userId;
private Integer status;
private Double rate;
private Instant createdTime;
private Instant submitTime;
private Medal medal;
private List<MedalTaskFile> taskFiles;
}
Copy the code
Reading,
methods | parameter | note |
---|---|---|
match() | (Criteria criteria) | Query condition construction |
lookup() | (String from, String localField, String foreignField, String as) | From: Associated table LocalField: master record associated field, passed in the name of the field in MongoDB, non-entity class field name ForeignField: Associated table associated field, field name is the same as above As: alias, and entity class mapping field name (lookup default return type ArrayList, related fields receive by default, similar to one-to-many mapping relationship) |
skip() | (int elementsToSkip)(long elementsToSkip) | Query start value |
limit() | (long maxElements) | Maximum number of elements, and page size |
sort() | (Sort sort) | Sort field |
unwind() | (String field) | an This field is mainly used for aggregate record splitting. It splits the master record corresponding to the corresponding collection field (length n) into N objects. The field name corresponds to a single document object in the collection PS: This code is used in the known one-to-one scenario to change the lookup associated object from an array to a single object. Therefore, Medal object can be used to receive this parameter in MedalUserTo |
project() | (String… fields) | Controls the display of query fields that can be used to return specified fields |
Unwind and project combined query examples in MongoDB
Normal query
// Check the data structure
db.operationLog.aggregate([
{$match:{_id:'DBDF7FA8C72C4606A15CF250FF35A530'}},
{$project:{_id:1,batchIdList:1}}])Copy the code
The results of
{
"_id" : "DBDF7FA8C72C4606A15CF250FF35A530"."batchIdList": [{"taskId" : "721EB7250C0049B49A5AB4A734498F2B"."batchInfos": {}}, {"taskId" : "E1C60CAD114D405B916E66A05256C07B"."batchInfos": {}}]}Copy the code
Split the query record using the unwind
db.operationLog.aggregate([
{$match:{_id:'DBDF7FA8C72C4606A15CF250FF35A530'}},
{$unwind:"$batchIdList"}])Copy the code
The results of
{
"_id" : "DBDF7FA8C72C4606A15CF250FF35A530"."batchIdList" : {
"taskId" : "721EB7250C0049B49A5AB4A734498F2B"."batchInfos": {}}} {"_id" : "DBDF7FA8C72C4606A15CF250FF35A530"."batchIdList" : {
"taskId" : "E1C60CAD114D405B916E66A05256C07B"."batchInfos": {}}}Copy the code
The Aggregation group, lookup, project, and unwind methods can be flexibly combined to simplify the code writing of associated query, statistical query, and Aggregation query on MongoDB