Aggregation is a very important data batch operation in CloudBase database. An aggregation operation can group data (or not group, i.e. only one group/one group for each record), perform multiple batch operations on each group of data, and return results.

With the aggregation capability, it is convenient to solve many scenarios that cannot be implemented or can only be implemented inefficiently without the aggregation capability, including group query, only taking the statistical value of certain fields or returning the transformation value, pipelined-phased batch processing, and obtaining unique values (de-duplication).

In this paper, with a simple example to explain how the database in the cloud, implementation is very commonly used league table + aggregation query operation, open cloud development: console.cloud.tencent.com/tcb?tdl_anc…

The scene that

Suppose there are two sets in the database: class and student, with the following data:

Class information:

Student Information:

Now we need to query the average score of all students in the class led by Teacher Xu.

Code sample

1. Lookup the linked table query

First we need to group all the data in student according to class_id. Here we use the lookup operator of the cloud database:

Lookup ({from: "student", //student localField: "id", //student localField: "class_id", //student localField: "class_id", // as: "Stu" // define an alias for the output array}).end();Copy the code

This statement will check the following result, which will check the information about the class and all students corresponding to that class:

{"list": [{" id ": 1," the teacher: "teacher wang", "cname" : "class", "stu" : [{" sname ":" a ", "class_id" : 1, "score" : 90}]}, {" id ": 2, "The teacher", "xu teacher", "cname" : "class 2", "stu" : [{" class_id ": 2," sname ":" a second ", "score" : 100}, {" class_id ": 2," sname ":" Li Er." "score":80 } ] }] }Copy the code

However, we only need the data of students in Teacher Xu’s class, so we need to further filter.

2, match condition match

Now it only returns the student data of Teacher Xu’s class, which is in the corresponding array of STU:

.lookup({from: 'student', localField: 'id', foreignField: 'class_id', as: 'stu'}).match({teacher:" teacher "}).end().lookup({from: 'student', localField: 'id', foreignField: 'class_id', as: 'stu'}).match({teacher:" Teacher "}).end()Copy the code

Now it only returns the student data of Teacher Xu’s class, which is in the corresponding array of STU:

[{{" a list ":" _id ":" 5 e847ab25eb9428600a512352fa6c7c4 ", "id" : 2, "the teacher" : "xu teacher", "cname" : "class 2", / / student data "stu" : 37 e26adb5eb945a70084351e57f6d717 [{" _id ":" ", "class_id" : 2, "sname" : "a second", "score" : 100}, {" _id ": "Five e847ab25eb945cf00a5884204297ed8", "class_id" : 2, "sname" : "Li Er", "score" : 80}}}]]Copy the code

Next, we optimize the code to return the student’s average score directly.

3, directly return the average score of students

If you want to aggregate a table (student in this course), use the pipeline method:

.lookup({ from: 'student', pipeline: $.pipeline() .group({ _id: null, score: $.avg('$score') }) .done(), as: 'stu'}).match({teacher:" teacher "}).end()Copy the code

The output now looks like this:

[{{" a list ":" _id ":" 5 e847ab25eb9428600a512352fa6c7c4 ", "id" : 2, "the teacher" : "xu teacher", "cname" : "class 2", "stu" : [{" _id ": null, "score": 90 }] } ] }Copy the code

But now the output data is a bit complicated. If we only want to display the two values of teacher and score, we will perform the following operation.

4. Only teacher and Score are displayed

We use replaceRoot, mergeObjects, and Project for the final processing:

.lookup({ from: 'student', pipeline: $.pipeline() .group({ _id: null, score: $.avg('$score') }) .done(), as: 'stu'}).match({teacher:" xu "}).replaceroot ({newRoot: $.mergeObjects([$.arrayElemAt(['$stu', 0]), '$$ROOT']) }) .project({ _id:0, teacher:1, score:1 }) .end()Copy the code

The output now looks like this:

{"list": [{"score": 90, "teacher": "xu"}]}Copy the code

Technical exchange Q group: 601134960

Latest news follow wechat official account [Tencent Yunyun Development]