I’m helping prepare the CFSummit conference. We’ve organized the sessions on Trello and had a public voting session. It’s time to start organizing the topics into a schedule.
In a conference schedule, it’s important to know which sessions will be popular. It’s desirable to ensure the most desirable sessions do not compete with each other. Thus, I wanted to pull out the sessions and organize the sessions by popularity.
The MongoDB Aggregation Framework
The MongoDB aggregation framework is a relatively new addition to the platform. Using this framework, you can group, sort, calculate and handle information gathering in the aggregate sense. Here’s how I did this for the Trello Json data.
The Mongo Query
Exporting out of Trello gives a big JSON document with JSON members for each card. It turns out, in our case, all of the cards we want belong to a specific list. Once we pull the correct cards, we want to sort them by their votes. We’ll end up with a sorted array of sessions by popularity. Here is the MongoDB query:
db.cfsummit.aggregate([
{$project: { "cards": "$cards"}},
{$unwind: "$cards"},
{$match: {"cards.idList": {"$in": ["51c9aa15d0b4871a3e000075"]}}},
{$project: {"_id": 1, "name": "$cards.name", "members": "$cards.idMembers", "url": "$cards.url", "votes": "$cards.badges.votes"}},
{$sort: {votes:-1}}
])
Explained Line by Line:
db.cfsummit.aggregate([
Notice the argument to the aggregate command is an array? This means you can organize a series of document transformations into steps. Each step will manipulate the document in some fashion. Let’s look at our first step in the transformation:
{$project: { “cards”: “$cards”}},
The first transformation is a $project command. Project (Pro-JECT), means to project a new way to view the data. In this case, I’m only interested in the cards node. The result of this document is a new document with basically only the cards member. You can write queries without $project, but I always do use it for 2 reasons. Firstly, reducing the size of the working document makes the query more efficient. The resulting projected document is smaller and can more easily be manipulated. The second reasons is I write my queries incrementally, so I only need to see, what I need to see. (Note the cards member is an array, this is important in the next step)
"result" : [
{
"_id" : ObjectId("51ee98afaa17829291af81e0"),
"cards" : [
{
"id" : "51b0fbec94b2237145005a18",
"badges" : {
"votes" : 0,
"viewingMemberVoted" : false,
"subscribed" : false,
.....
{$unwind: “$cards”},
Now the card nodes is an array. I’m going to want to sort all of the matching cards by the votes parameter. I use an $unwind command to transform the cards array members into their own documents.
"result" : [
{
"_id" : ObjectId("51ee98afaa17829291af81e0"),
"cards" : {
"id" : "51b0fbec94b2237145005a18",
"badges" : {
"votes" : 0,
"viewingMemberVoted" : false,
"subscribed" : false,
...
Note, the cards member is no longer an array… this is important for grouping, which we will do later.
{$match: {“cards.idList”: {“$in”: [“51c9aa15d0b4871a3e000075”]}}}
Each of the cards we want to deal with belongs to listId: 51c9aa15d0b4871a3e000075. So we use the $match command to match the cards with the listId we are looking for. (Think of this like a where clause in SQL, if that is your background.
"result" : [
{
"_id" : ObjectId("51ee98afaa17829291af81e0"),
"cards" : {
"id" : "51b0fbec94b2237145005a18",
"badges" : {
"votes" : 0,
"viewingMemberVoted" : false,
"subscribed" : false,
...
{$project: {“_id”: 1, “name”: “$cards.name”, “members”: “$cards.idMembers”, “url”: “$cards.url”, “votes”: “$cards.badges.votes”}},
Now I have my sorted cards belonging to the correct list. I now want to set up the return data structure in a way that is most useful to me. In my case, I want the ID, Name of the Session, The Presenters, The Trello URL for the card and the Votes Received. We once again use a $project command to organize the data in the format we want. Note, I’ve used a dot delimited path to walk the JSON tree to the data member I want. Hence, the votes were in the Votes Node which is inside the Badges Node which is inside the Cards node.
{$sort: {votes:-1}}
Lastly, we need to sort the cards by their popularity. The $sort command takes a JSON object containing the nodes you want to sort by. We want most votes to appear first, so we assign a -1 to the votes column for descending sort. Changing this to 1, would sort the data in an ascending manner.
Final Data Result
{
"result" : [
{
"_id" : "51ee98afaa17829291af81e0",
"name" : "Security Best Practices",
"members" : [
"51b0ff9bf9d2b2b94c0027bd"
],
"url" : "https://trello.com/c/ITpzm0xS/15-security-best-practices",
"votes" : 45
},
{
"_id" : "51ee98afaa17829291af81e0",
"name" : "ColdFusion Object Oriented Advanced",
"members" : [
"519a266b522736c97000a224"
],
"url" : "https://trello.com/c/1DV4Ud2Z/41-coldfusion-object-oriented-advanced",
"votes" : 43
},
{
"_id" : "51ee98afaa17829291af81e0",
"name" : "REST 101",
"members" : [
"50997edfdcb1ac3f1c00ac66"
],
"url" : "https://trello.com/c/1oYg37pV/23-rest-101",
"votes" : 34
},
....
Want More Information?
Learn more about the MongoDB Aggregation Framework at their documentation site. You can install MongoDB in very little time and start working with data.
Free Training
If you want a more structured training, 10Gen offers a 7 week online training class on MongoDB for free. The classes are very well done. Consider a class if you are Mongo-Curious.
