Ranking is popular in all industries and organizations around the world, especially in this digital era. Generally most databases provide good support of calculating rankings. Unfortunately MongoDB isn’t among those offering such a support. The NoSQL database can get elements in a nested array by indexes, but it can’t get indexes according to elements. This means MongoDB isn’t able to find rankings of the elements in an array. If we store names in a MongoDB array according to their rankings, the database can find a name by its ranking (index) but can’t get the ranking (index) for its name. That is, we can find it is Tom who ranks first but can’t find Tom’s ranking through his name in MongoDB. We can use SPL (Structured Process Language) to get MongoDB indexes conveniently. Here’s how it does it.

The MongoDB collection users has name field and friends field that contains array values. Names in each array are ordered by their rankings. Below is part of the source data:

> db.users.find({"name":"jim"})
{
"_id" : ObjectId("544f3bf8cdb02668db9ab229"), "name" : "jim", "friends" : ["tom", "jack", "luke", "rose", "james", "sam", "peter"]
}

MongoDB finds the name at the specified ranking, such as jim’s friend who ranks first in the following way:
> db. users.find({"name":"jim"},{"friends":{"$slice":[0,1]}})
{
"_id" : ObjectId("544f3bf8cdb02668db9ab229"), "name" : "jim", "friends" : ["tom"]
}

We can’t find one of jim’s friends, luke’s ranking in MongoDB. But we can do it easily in SPL:
SPL script:

A1: Connect to MongoDB database on the local machine. The IP address and port number is localhost:27017. Both the user name and the password are test. You can set other parameters according to the connection string format mongo://ip:port/db?arg=value&….
A2: MongoDB’s find function retrieves data from users according to the filtering condition name=jim and by the keys name and friends and returns a cursor. SPL uses a cursor to batch retrieve and process data to in case of memory overflow.
A3: As we only have a relatively small amount of data, we use fetch() to get all records from the cursor.
A4: pos() function gets luke’s ranking. Here’s the final result:

In summary, SPL retrieves data by the specified condition and then use pos() function to get the ranking of the specified name. The code is concise and easy to understand.

Note: esProc doesn’t include the MongoDB Java driver. You need to put the driver jars, such as mongo-java-driver-3.9.1.jar, under esProc’s external library directory extLib\MongoCli in advance.

The SPL script can be easily integrated into a Java application. You just need to add one more line (return A4) to the script to output the result set to Java. Be sure to put the MongoDB Java driver jar into the Java application’s classpath beforehand.