MongoDB Blog @mongoblog - Tumblr Blog

Posts

mongodb aggregation using scala (2/2)

This is a follow up on the 1st part of the post.

In this second part, we will see one more example of the aggregation and some of the design choices.

Let's assume we have a collection "projectTracker" filled with documents that look like:

[ {

"userId" : "uid1", "fname" : "Amine" "lname" : "Ferchichi", "project" : "AGCOD" },

{ "fname" : "Peter", "userId" : "uid3", "lname" : "Coons", "project" : "CM" },

{ "fname" : "Amine", "userId" : "uid1", "lname" : "Ferchichi", "project" : "MeC" },

{ "userId" : "uid3", "fname" : "Peter", "lname" : "Coons", "project" : "MeC" },

{ userId" : "uid4", "fname" : "John", "lname" : "Smith", "project" : "MeC" },

{ "userId" : "uid2", "fname" : "Fred", "lname" : "Bohan", "project" : "CM" },

{ "userId" : "uid1", "fname" : "Amine", "lname" : "Ferchichi", "project" : "PF" },

{ "userId" : "uid3", "fname" : "Peter", "lname" : "Coons", "project" : "PF" },

{ "userId" : "uid2", "fname" : "Fred", "lname" : "Bohan", "project" : "RecEng" }

]

Now let's try to get the projects that both users "uid1" and "uid3" worked on. But first, let's break down the problem:

1) Filter projects to get only the ones that were worked on by "uid1" OR "uid3".

2) Group projects by userIds, and add them into a temporary array.

3) Filter out the results that do not contain, both, "uid1" and "uid3"

// step 1: 1st Filtering val firstMatchStatement = MongoDBObject("$match" -> MongoDBObject("userId" -> MongoDBObject("$in" -> MongoDBList("uid1", "uid3")))) // step 2: Grouping val groupStatement = MongoDBObject("$group" -> MongoDBObject( "_id" -> MongoDBObject("project" -> "$project"), "tempUsers" -> MongoDBObject("$push" -> MongoDBObject("userId" -> "$userId")) )) // step 3: 2nd Filtering

val secondMatchStatement = MongoDBObject("$match" -> MongoDBObject("$and" : MongoDBList(MongoDBObject("tempUsers.userId" -> "uid1"), MongoDBObject("tempUsers.userId" -> "uid3")))

val pipeLine = MongoDBList(firstMatchStatement , groupStatement , secondMatchStatement )

val result = db.command(MongoDBObject("aggregate" -> "projectTracker", "pipeline" -> pipeline)).get("result")

Now, you might be wondering why is the pipeline getting a DBList as argument, as opposed to a straight json/DBObject just like the MongoDB doc says. Well, in this case, we have two $match clauses, having a DBObject (basically a key value map) will simply override the 1st match with the 2nd one, hence the usage of a DBList.

#MongoDB #mongo #mongo array #amine #amine ferchichi #ferchichi #aggregation #aggregation framework #framework #groupby #grouping #scala

MongoDB aggregation using Scala (1/2)

This is something that you probably were looking for if you're using Casbah and recently upgraded mongo to support the famous aggregation framework.

In this post we will work through how to add this implementation to your code base, and a simple example that will cover most of the use cases for the aggregation pipeline.

Let's assume we have a collection "city" filled with documents that look like:

[ { "name" : "Washington", "state" : "DC", "coast" : "EAST", "population" : 617996, "mayor" : "Vincent C. Gray" }, { "name" : "Baltimore", "state" : "MD", "coast" : "EAST", "population" : 619493, "mayor" : "Stephanie R. Blake" }, { "name" : "Annapolis", "state" : "MD", "coast" : "EAST", "population" : 38880, "mayor" : "Joshua J. Cohen" }, { "name" : "Philadelphia", "state" : "PA", "coast" : "EAST", "population" : 1536471, "mayor" : "Michael A. Nutter" }, { "name" : "Scranton", "state" : "PA", "coast" : "EAST", "population" : 75995, "mayor" : "Christopher Doherty" }, { "name" : "San Francisco", "state" : "CA", "coast" : "WEST", "population" : 805235, "mayor" : "Edwin M. Lee" }, { "name" : "Sacramento", "state" : "CA", "coast" : "WEST", "population" : 472178, "mayor" : "Kevin Johnson" } ]

Now let's try to get the mayor of the most populated city for each state that is on the East cost. But first, let's break down the problem into smaller steps:

1) Filter out the non east-coast cities.

2) Sort the cities by population.

3) Group cities by state, and return the mayor name.

// step 1: Filtering val matchStatement = MongoDBObject("$match" -> MongoDBObject("coast" -> "EAST")) // step 2: Sorting val sortStatement = MongoDBObject("$sort" -> MongoDBObject("population" -> -1)) // step 3: Grouping val groupStatement = MongoDBObject("$group" -> MongoDBObject( "_id" -> MongoDBObject("state" -> "$state"), "mayor" -> MongoDBObject("$first" -> "$mayor"), "population" -> MongoDBObject("$first" -> "$population") )) val pipeLine = MongoDBList(matchStatement, sortStatement, groupStatement) val result = db.command(MongoDBObject("aggregate" -> "city", "pipeline" -> pipeline)).get("result")

Now, let's explain a bit of what happened here:

1) $match: Pretty straight forward clause that will only forward filtered states to the group statement. One can argue that this step can be done after the grouping, but (in case of a huge set of data) that will simply use much more memory/time to process since all this computation takes place in your mongo box RAM.

2) $sort: This will help fetching the highest population when grouping by using the $first operator. This step can be replaced by $max within the $group clause.

3) $group: As any group statement, this will group by the "_id" passed which is the "state" field in our case (please note that when using the aggregation framework, the DB fields should be preceded with $, it's also highly recommended to name the fetched field the same as the parsed). Also as we used the $sort prior to the $group, we can take advantage of the $first operator that will 'hold' the first document for each (state) group.

#mongo #MongoDB #aggregation #framework #scala #groupby #grouping #aggregation framework #amine #ferchichi #amine ferchichi #sort #match #pipeline

Customize mongo shell prompt (v1.9.1+ only)

If you are reading this, you are probably still using the basic Mongo shell prompt:

In this article we will walk through some simple steps and show different options to have a prompt that will show a little more than just the line pointer, like :

careverge-11:12:01>

where careverge is the DB name and, you probably guessed it, the time. Again, this feature is only available for mongo v1.9.1 and higher.

Setting your prompt is as simple as:

> prompt = function() { ... var now = new Date(); ... return db+"-"+ ... now.getHours()+":"+now.getMinutes()+":"+now.getSeconds()+"> "; ... }

careverge-11:16:39> db.user.count()

careverge-11:16:42> 169

As you just saw, we just set up the prompt in few seconds, and one method. But this can be a pain to define every time you launch the mongo shell. The solution is to simply store you prompt function in a js file, and load it on start:

> load("my/path/monogoShellCfg.js")

Or, even better, you can store this (and many others) function in '.mongorc.js' file (under your home directory). '.mongorc.js' is a config file that mongo loads automatically, if it exists, on startup. One thing I find really useful to add to this file, is overriding the getDB() method to set the slaveOk to true by default. To do so, add this to your '.mongorc.js':

Mongo.prototype.getDB = function(name) { this.setSlaveOk(); return new DB(this, name); }

#MongoDB #mongo #amine #amine ferchichi #ferchichi #shell #mongo shell

Multi-document update & Atomicity

Documents updates in mongo are pretty straight forward when it comes to updating one document at a time. But updating multiple documents isn't really that different, you just have to be aware of its pseudo-atomicity.

Let's assume we have a collection 'foo'

that contains a list of documents

> db.foo.find()

[

{"_id" : ObjectId("4ec2e036e2a13727b"), "text" : "bar", "value" : 11},

{"_id" : ObjectId("4efba63c4d6a58d47"), "text" : "foo", "value" : 22},

{"_id" : ObjectId("4ecee6f9e2534534a"), "text" : "foo bar", "value" : 33},

{"_id" : ObjectId("4e9e46f9e2a4324a0"), "text" : "bar foo", "value" : 44}

]

Now let's change the text to "blah" for every object that has a value higher than 30:

> db.foo.update({"value" : {$gt : 30}}, {$set : {"text" : "blah"}})

> db.foo.find()

[

{"_id" : ObjectId("4ec2e036e2a13727b"), "text" : "bar", "value" : 11},

{"_id" : ObjectId("4efba63c4d6a58d47"), "text" : "foo", "value" : 22},

{"_id" : ObjectId("4e9e46f9e2a4324a0"), "text" : "bar foo", "value" : 44},

{"_id" : ObjectId("4ecee6f9e2534534a"), "text" : "blah", "value" : 33}

]

Running this query updated only one document, why? Because Mongo updates affect (natively) only the 1st relevant object.

Looking closer at the update command, we can see that it takes a query, update, upsert, and multi as arguments, where query is your condition, update is the update object, upsert is a boolean flag (set to false by default) that creates a document if we don't have any match against the query, and, yes you guessed it, the multi is a boolean flag (set to false by default) that forces the update to run against all relevant document. But as said above, this is a pseudo-atomic operation (it will allow some other operations, which could be writes, to interleave ). To avoid this issue, we simply need to add '$atomic : 1' to the query statement like so:

> db.foo.update({$atomic : 1, "value" : {$gt : 30}}, {$set : {"text" : "mamba"}}, false, true)

> db.foo.find()

[

{"_id" : ObjectId("4ec2e036e2a13727b"), "text" : "bar", "value" : 11},

{"_id" : ObjectId("4efba63c4d6a58d47"), "text" : "foo", "value" : 22},

{"_id" : ObjectId("4e9e46f9e2a4324a0"), "text" : "mamba", "value" : 44},

{"_id" : ObjectId("4ecee6f9e2534534a"), "text" : "mamba", "value" : 33}

]

Et Voilà!

#mongo #mongodb #update #multiple #atomic #atomicity #upsert #multi #how to update multiple documents #$atomic #mongodb atomic #mongo atomic #mongodb atomicity #mongo atomicity #mongo update #mongodb update

Update if Current

"Update if Current" is a manual way to insure atomicity during an update, and for this we:

Fetch the object we want to update.

Modify the object locally.

Update where the condition should match the old value (from step 1)

Let’s assume we have a collection ‘foo’ like so:

> db.foo.find().pretty()

{

"_id" : ObjectId("4f9808648859c65d"),

"array" : [

{"text" : "foo", "value" : 11},

{"text" : "bar", "value" : 22},

{"text" : "foobar", "value" : 33}

]

}

And let's say we want to update the array to change the text to "blah" for every field that has a value greater than 20. As we saw in the previous post, Mongo supports updates within the array only for the first relevant match (where text = "bar" in this case) but we would like to modify the one that has 33 as value since it's a match.

We, first, fetch the object we want: var obj = db.foo.findOne({"_id" : ObjectId("4f9808648859c65d")}) var old_array = obj.get("array")

Then we update the array locally to: var new_array = [

{"text" : "foo", "value" : 11},

{"text" : "blah", "value" : 22},

{"text" : "blah", "value" : 33}

]

We update the collection foo where the array matches the old_array: db.foo.update( {"_id" : ObjectId("4f9808648859c65d"), "array" : old_array} ,{$set : {"array" : new_array}})

This is a 'dirty' solution that will insure the atomic update for this case. A better way to do this would be to keep a version variable in the object, and increment it on each update.

#MongoDB #mongo #update #mongo atomic #mongo atomicity #update if current #amine #ferchichi #amine ferchichi #mongo array

Updating one element in an array

Mongo updates are usually easy and pretty clear (if you are familiar with JSON format), but it has its own limitations, especially when it comes to updating fields within an array.

Let's assume we have a collection 'foo' like so:

> db.foo.find().pretty()

{

"_id" : ObjectId("4f9808648859c65d"),

"array" : [

{"text" : "foo", "value" : 11},

{"text" : "foo", "value" : 22},

{"text" : "foobar", "value" : 33}

]

}

And now we would like to update the text of the element in the array 'array' to "blah" where the value is 22:

> db.foo.update({"array.value" : 22}, {"$set" : {"array.$.text" : "blah"}})

> db.foo.find().pretty()

{

"_id" : ObjectId("4f9808648859c65d"),

"array" : [

{"text" : "foo", "value" : 11},

{"text" : "foobar", "value" : 33},

{"text" : "blah", "value" : 22}

]

}

So, as you just saw, the trick is the dollar sign '$' (called the $ positional operator) which saves the index (1 in our case) of the element from the array that matched the query. This means that if we knew the position of the element (which is nearly impossible in a real life case), we will just change the update statement to : {"$set" : {"array.1.text" : "blah"}}.

Please note that the $ positional operator (for now) updates the first relevant document ONLY. This means if the query was to update all elements where the value is greater than 10, it will just update the element where value is 10, and skip the other two (10gen are still working on this, and here's the ticket for it).

But you can always work around this, and the laziest way is to read the entire document, update it manually, and then save it back. You must be careful with this process since it isn't the safest: the same object might be subject to other updates while you're manually updating the array. To avoid that, the simplest way is to use the 'update if current' (will be the topic of the next post).

#mongodb #mongo #update #mongo update #mongodb update #update array #update element #update in array #mongo array #mongo update array #mongodb array #mongodb arrau update #positional operator #dollar positional #dollar operator #$ operator #$ positional operator

Trending Blogs

Recently Viewed Blogs

MongoDB Blog