Skip to main content

CloudBase Cloud Database Implements Join + Aggregation Queries

Scenario Description​

We assume there are two collections in the database, class and student, containing 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 Teacher Xu's class.

Code Sample​

1. lookup Join Query​

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

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

This statement will return the following result, retrieving the class information and all corresponding student information for that 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 data for students in Teacher Xu's class, so further filtering is required.

2. match condition matching​

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

Now only the student data from Teacher Xu's class is returned, with the student data located 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 students.

3. Direct return of average student scores​

If you want to perform aggregation operations on the joined collection (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 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 }]
    }
  ]
}

But now the output data is a bit complex. If you only want to display the teacher and score values, we'll proceed with the following operations.

4. Display only 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 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" }] }