Group MongoDB Collection and Find Top N members in esProc

Problem source:https://groups.google.com/forum/#!topic/mongodb-user/Rjv6lyUCe1s .

Collection last3 has two fields: variable and timestamp. You need to first group documents by variable and find from each group the top 3 ones with the latest timestamp, and then find from the three documents the one with the earliest timestamp.

Below is the selection from last3:

esProc_NoSQL_mongodb_grouptopN_1

esProc code

esProc_NoSQL_mongodb_grouptopN_2

A1: Connect to MongoDB. The connection string format is mongo://ip:port/db?arg=value&…

A2: Use find function to retrieve data from MongoDB, sort it and create a cursor. last3 is the collection name; no filtering criterion is specified; and all fields except _id will be retrieved and sorted by variable. In esProc find function, which is analogous to the combination of MongoDB find, sort and limit function, the filtering criterion syntax follows the MongoDB rules.

A3: Fetch data from the cursor by loop, getting a group of documents with the same variable field each time. A3’s working range is the indented B3 to B4, where A3 can be used to reference the loop variable. A3’s result is in-memory data. The following is one of the results of data fetching:

esProc_NoSQL_mongodb_grouptopN_3

B3: Find from the current group of documents the three ones with the latest timestamp.

B4: Append each of B3’s loop results to B2. The result of B2 is as follows:

esProc_NoSQL_mongodb_grouptopN_4

A5Find the document with the earliest timestamp from B2. It is as follows:

esProc_NoSQL_mongodb_grouptopN_5

A6Close MongoDB connection.

 

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 and tagged , , , , . 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