Problem source：http://stackoverflow.com/questions/29392169/populating-field-values-for-referred-documents-in-aggregate-call-in-mongoose .
With MongoDB’s built-in API, you implement a foreign key relationship through hardcoding. The hardcode is not intuitive and difficult to write. In this case you can use esProc to handle this. For example:
Collection UserCourseProgres records the relationship between users and courses. Its courseid is the foreign key that points to _id field in Collection Course. You need to find the number of users who study each course. Course names use title field in Course.
esProc code：
| A | |
| 1 | =MongoDB(“mongo://localhost:27017/local?user=test&password=test”) |
| 2 | =A1.aggregate(“UserCourseProgress”, [{$group: {‘_id’: {‘primary’: ‘$courseid’}, popularityCount’: {$sum: 1}}},{$sort:{‘popularityCount’:-1}},{$project:{‘_id’:0,’courseid’:’$_id.primary’,’popularityCount’:1}}]”) |
| 3 | =A1.find(“Course”,,”{title:1}”).fetch() |
| 4 | =A2.switch(courseid,A3:_id) |
| 5 | =A4.new(popularityCount,courseid.title) |
| 6 | =A1.close() |
A1: Connect to MongoDB. The connection string format is mongo://ip:port/db?arg=value&…
A2: Compute the number of people who study each course. Here aggregate function is used to retrieve data from MongoDB. This function derives from MongoDB. Its first parameter is collection name and the second one is the aggregate expression whose syntax follows MongoDB rules. A2’s result is in-memory data, as shown below:
A3: Find course names from Course. find function is used here to retrieve data from MongoDB. This function derives from MongoDB. Its second parameter is the filtering criterion whose syntax follows MongoDB rules. The function returns a cursor. Since there are only a few courses, fetch function is used to fetch cursor data into memory. The result is as follows:
A4: Switch A3’s foreign key values into corresponding records in A2. The result is:
A5：Access in-memory data using object and create a new two-dimensional table as follows:
A6：Disconnect from MongoDB.