MongoDB::Examples - Some examples of MongoDB syntax
For developers familiar with SQL, the following chart should help you see how many common SQL queries could be expressed in MongoDB.
These are Perl-specific examples of translating SQL queries to MongoDB's query language. To see the JavaScript (or other languages') mappings, see http://dochub.mongodb.org/core/sqlToMongo.
CREATE TABLE USERS (a Number,
b Number) Implicit,
can be done explicitly.INSERT INTO USERS VALUES(1,1) $db->users->insert({a => 1, b => 1});
SELECT a,b FROM users $db->users->find({}, {a => 1, b => 1});
SELECT * FROM users$db->users->find;
SELECT * FROM users WHERE age=33 $db->users->find({age => 33})
SELECT a,b FROM users WHERE age=33 $db->users->find({age => 33}, {a => 1, b => 1});
SELECT * FROM users WHERE age=33 ORDER BY name $db->users->find({age => 33})->sort({name => 1});
<SELECT * FROM users WHERE age33>> $db->users->find({age => {'$gt' => 33}})
<SELECT * FROM users WHERE age<33> $db->users->find({age => {'$lt' => 33}})
SELECT * FROM users WHERE name LIKE "%Joe%" $db->users->find({name => qr/Joe/});
SELECT * FROM users WHERE name LIKE "Joe%" $db->users->find({name => qr/^Joe/});
<SELECT * FROM users WHERE age33 AND age<=40>> $db->users->find({age => {'$gt' => 33, '$lte' => 40}});
SELECT * FROM users ORDER BY name DESC $db->users->find->sort({name => -1});
CREATE INDEX myindexname ON users(name) $db->users->ensure_index({name => 1});
CREATE INDEX myindexname ON users(name,ts DESC)$db->users->ensure_index(Tie::IxHash->new(name => 1, ts => -1));
SELECT * FROM users WHERE a=1 and b='q' $db->users->find({a => 1, b => "q"});
SELECT * FROM users LIMIT 10 SKIP 20$db->users->find->limit(10)->skip(20);
SELECT * FROM users WHERE a=1 or b=2 $db->users->find({'$or' => [{a => 1}, {b => 2}]});
SELECT * FROM users LIMIT 1$db->users->find->limit(1);
EXPLAIN SELECT * FROM users WHERE z=3 $db->users->find({z => 3})->explain;
SELECT DISTINCT last_name FROM users $db->run_command({distinct => "users", key => "last_name"});
SELECT COUNT(*y) FROM users$db->users->count;
<SELECT COUNT(*y) FROM users where age 30>> $db->users->find({"age" => {'$gt' => 30}})->count;
SELECT COUNT(age) from users $db->users->find({age => {'$exists' => 1}})->count;
UPDATE users SET a=1 WHERE b='q' $db->users->update({b => "q"}, {'$set' => {a => 1}});
UPDATE users SET a=a+2 WHERE b='q' $db->users->update({b => "q"}, {'$inc' => {a => 2}});
DELETE FROM users WHERE z="abc" $db->users->remove({z => "abc"});
The distinct command returns all values for a given key in a collection. For example, suppose we had a collection with the following documents (_id value ignored):
{ 'name' => 'a', code => 1 }
{ 'name' => 'b', code => 1 }
{ 'name' => 'c', code => 2 }
{ 'name' => 'd', code => "3" }
If we wanted to see all of values in the "code" field, we could run:
my $result = $db->run_command([
"distinct" => "collection_name",
"key" => "code",
"query" => {}
]);
Notice that the arguments are in an array, to ensure that their order is preserved. You could also use a Tie::IxHash.
query is an optional argument, which can be used to only run distinct on specific documents. It takes a hash (or Tie::IxHash or array) in the same form as "find($query)" in MongoDB::Collection.
Running distinct on the above collection would give you:
{
'ok' => '1',
'values' => [
1,
2,
"3"
]
};
The find-and-modify command is similar to update (or remove), but it will return the modified document. It can be useful for implementing queues or locks.
For example, suppose we had a list of things to do, and we wanted to remove the highest-priority item for processing. We could do a "find" in MongoDB::Collection and then a "remove" in MongoDB::Collection, but that wouldn't be atomic (a write could occur between the query and the remove). Instead, we can use find and modify.
my $next_task = $db->run_command({
findAndModify => "todo",
sort => {priority => -1},
remove => 1
});
This will atomically find and pop the next-highest-priority task.
See http://www.mongodb.org/display/DOCS/findAndModify+Command for more details on find-and-modify.
The group command is similar to "GROUP BY" in SQL. You can use the "run_command" in MongoDB::Database method to perform group-bys with MongoDB.
For example, suppose we have a number of local businesses stored in a "business" collection. If we wanted to find the number of coffeeshops in each neighborhood, we could do:
my $reduce = <<REDUCE;
function(doc, prev) {
for (var t in doc.tags) {
if (doc.tags[t] == "coffeeshop") {
prev["num coffeeshops"]++;
break;
}
}
}
REDUCE
my $result = $db->run_command({group => {
'ns' => "business",
'key' => {"neighborhood" => 1},
'initial' => {"num coffeeshops" => 0},
'$reduce' => MongoDB::Code->new(code => $reduce)
This would return something like:
{
'ok' => '1',
'keys' => 4,
'count' => '487', # total number of documents
'retval' => [
{
'neighborhood' => 'Soho',
'num coffeeshops' => '23'
},
{
'neighborhood' => 'Chinatown',
'num coffeeshops' => '14'
},
{
'neighborhood' => 'Upper East Side',
'num coffeeshops' => '10'
},
{
'neighborhood' => 'East Village',
'num coffeeshops' => '87'
}
]
};
Thus, there are 23 coffeeshops in Soho, 14 in Chinatown, and so on.
See http://www.mongodb.org/display/DOCS/Aggregation for more details on grouping.
MapReduce is a powerful aggregation tool. (For traditional queries, you should use MongoDB::Collection::query.)
This example counts the number of occurences of each tag in a collection. Each document contains a "tags" array that contains zero or more strings.
my $map = <<MAP;
function() {
this.tags.forEach(function(tag) {
emit(tag, {count : 1});
});
}
MAP
my $reduce = <<REDUCE;
function(prev, current) {
result = {count : 0};
current.forEach(function(item) {
result.count += item.count;
});
return result;
}
REDUCE
my $cmd = Tie::IxHash->new("mapreduce" => "foo",
"map" => $map,
"reduce" => $reduce);
my $result = $db->run_command($cmd);
See the MongoDB documentation on MapReduce for more information (http://dochub.mongodb.org/core/mapreduce).
In MongoDB 1.3.4 and later, you can use positional operator, $, to update elements of an array. For instance, suppose you have an array of user information and you want to update a user's name.
A sample document in JavaScript:
{
"users" : [
{
"name" : "bill",
"age" : 60
},
{
"name" : "fred",
"age" : 29
},
]
}
The update:
$coll->update({"users.name" => "fred"}, {'users.$.name' => "george"});
This will update the array so that the element containing "name" => "fred" now has "name" => "george".
