esProc Implements Foreign Key Relationship for MongoDB Collections

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_NoSQL_mongodb_foreignkey_1

esProc code

esProc_NoSQL_mongodb_foreignkey_2

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:

esProc_NoSQL_mongodb_foreignkey_3

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:

esProc_NoSQL_mongodb_foreignkey_4

A4: Switch A3’s foreign key values into corresponding records in A2. The result is:

esProc_NoSQL_mongodb_foreignkey_5

A5Access in-memory data using object and create a new two-dimensional table as follows:

esProc_NoSQL_mongodb_foreignkey_6

 

A6Disconnect from MongoDB.

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in MongoDB, SQL-related Puzzle. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s