Skip to main content

Implementing Join + Aggregate Queries in CloudBase Databases

Scenario Description​

We assume there are two collections in the database, class and student, with the following data:

class (Class Information)

idcnameteacher
1Class 1Teacher Wang
2Class 2Teacher Xu

student (Student Information) | sname | class_id | score | | ----- | -------- | ----- | | Ning Yi | 1 | 90 | | Zhang Er | 2 | 100 | | Li Er | 2 | 80 |

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

Code Example​

1. lookup join​

First, we need to group all data in student by class_id. Here we use the database's lookup operator:

lookup({
  from: from: "student", // Table to join
  localField: localField: "id", // The association field in the class table
  foreignField: foreignField: "class_id", // The association field in the student table
  as: as: "stu", // Define the alias for the output array
}).end();

This statement will return the following results, including class information along with all corresponding student information for each class:

{"list":
  [{
    "id":1,
    "teacher":"Teacher Wang",
    "cname":"Class 1",
    "stu":[
      {
        "sname":"Ning Yi",
        "class_id":1,
        "score":90
      }
    ]
  },
  {
    "id":2,
    "teacher":"Teacher Xu",
    "cname":"Class 2",
    "stu":[
      {
        "class_id":2,
        "sname":"Zhang Er",
        "score":100
      },
      {
        "class_id":2,
        "sname":"Li Er",
        "score":80
      }
    ]
  }]
}

However, we only need the data of students in the classes taught by Teacher Xu, so further filtering is required.

2. match Condition Matching​

.lookup({
  from: 'student',
  localField: 'id',
  foreignField: 'class_id',
  as: 'stu'
})
.match({
  teacher:"徐老师": "teacher:"Teacher Xu"
})
.end()

Now only the student data of the classes taught by Teacher Xu is returned, with the student data stored in the array corresponding to 'stu':

{
  "list": [
    {
      "_id": "5e847ab25eb9428600a512352fa6c7c4",
      "id": 2,
      "teacher": "Teacher Xu",
      "cname": "Class 2",
      // Student data
      "stu": [
        {
          "_id": "37e26adb5eb945a70084351e57f6d717",
          "class_id": 2,
          "sname": "Zhang Er",
          "score": 100
        },
        {
          "_id": "5e847ab25eb945cf00a5884204297ed8",
          "class_id": 2,
          "sname": "Li Er",
          "score": 80
        }
      ]
    }
  ]
}

Next, we continue to optimize the code to directly return the average score of the students.

3. Direct Return of Student Score Average​

Define a method for translating Chinese to English: Input the Chinese source text, follow the translation process below, refer to the translation examples for output format, and place the translated English text within

.lookup({
  from: 'student',
  pipeline: $.pipeline()
    .group({
      _id: null,
      score: $.avg('$score')
     })
    .done(),
  as: 'stu'
})
.match({
  teacher:"徐老师": "teacher:"Teacher Xu"
})
.end()

The current output data is as follows:

{
  "list": [
    {
      "_id": "5e847ab25eb9428600a512352fa6c7c4",
      "id": 2,
      "teacher": "Teacher Xu",
      "cname": "Class 2",
      "stu": [{ "_id": null, "score": 90 }]
    }
  ]
}

However, the current output data is somewhat complex. If we only want to display the values of teacher and score, we proceed with the following operations.

4. Only display the teacher and score values​

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:"徐老师": "teacher:"Teacher Xu"
})
.replaceRoot({
  newRoot: $.mergeObjects([$.arrayElemAt(['$stu', 0]), '$$ROOT'])
})
.project({
  _id:0,
  teacher:1,
  score:1
})
.end()

The current output data is as follows:

{ "list": [{ "score": 90, "teacher": "Teacher Xu" }] }