Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Using MongoDB Aggregation Framework to Filter Trello Cards

DZone's Guide to

Using MongoDB Aggregation Framework to Filter Trello Cards

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

When I was helping prepare for the CFSummit conference, we organized the sessions on Trello and had a public voting session.

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)

 view formatted print about
"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.

])

Don't forget to close out your aggregation pipeline array and close the function parenthesis.

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.




It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:

Published at DZone with permission of Dan Wilson, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}