Solving development problems  |  About this blog

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,
  }
]