MongoDB can use stored procedures similar to MSSQL. To make a MongoDB procedure create a new file, name it like DoSomething.js, write a function and attach it to database.
Guide through example
Lets say we have a log of website visitors stored in visitors collection inside myweb database. Each visitors record contains date and time of a visit. Now we want to create monthly statistics – how many users visits website per month.
visitors collection looks like:
> use myweb
> db.visitors.findOne()
{
"_id": ObjectId("4e0cb7e7da3ea11d18d841e7"),
"timestamp": "Thu Jun 30 2011 19:52:39 GMT+0200 (Central Europe Daylight Time)"
}
Most reusable way to get data is to create a stored procedure GetVisitsPerMonth() and call it on demand. Create a file GetVisitsPerMonth.js and copy-paste the following code inside. Note that file name is named by procedure.
function ()
{
var result = db.visitors.group(
{
keyf: function(doc)
{
return { //NOTE: Bracket must be in this line!!!
month: doc.timestamp.getMonth(),
year: doc.timestamp.getFullYear()
};
},
initial: {count:0},
reduce: function(doc, prev) { prev.count++ }
});
return result;
}
How to attach one procedure
Stored procedure can be manually attached like this:
db = connect("localhost:27017/myweb");
db.system.js.save({"_id":"GetVisitsPerMonth", "value": function() { ... });
How to attach multiple procedures
Large sets of procedures should be well organized in files so you can quickly find a procedure and fix it if necessary. To insert or update all procedures at once use a simple batch script.
Create a new file, name it install.bat and copy-paste the following code inside:
@echo off
:parameters
set DBCON=localhost:27017/myweb
set MONGO=c:\mongodb\bin\mongo.exe
set SCRIPT=script.js
:startup
if not exist %MONGO% goto error
if not "%1"=="" goto add
rem Append connection string
echo Adding connection string...
echo db = connect("%DBCON%"); > %SCRIPT%
echo. >> %SCRIPT%
rem Append scripts
call %0 GetVisitsPerMonth
rem call %0 DoSomething
rem other procedures go here
goto install
rem goto end
:add
echo Adding script %1...
echo db.system.js.save({"_id":"%1", "value": >> %SCRIPT%
type %1.js >> %SCRIPT%
echo }); >> %SCRIPT%
echo. >> %SCRIPT%
goto end
:install
echo Running script...
%MONGO% %SCRIPT%
goto end
:error
echo Check if mongo is installed:
echo %MONGO%
:end
Edit parameters according to your needs: path to mongo.exe, database name, and list of procedures.
Simply run the script and it is done.
Testing a stored procedure
Log into mongo console:
> use myweb
> db.eval("GetVisitsPerMonth()")
[
{
"month": 5,
"year": 2011,
"count": 28233,
},
{
"month": 6,
"year": 2011,
"count": 48026,
},
{
"month": 7,
"year": 2011,
"count": 92754,
}
]
