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

  1. Query the medal task name and related configuration information in the medal task main table

  2. 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

  1. Select MedalId and taskIds from MedalTask

  2. 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