Extracting Data from a MongoDB with onboard resources

Let's start with the famous "Hello, World!" program hello-world.js:

print("Hello, World!")

Executing mongo hello-world.js will result in an output like this:

MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.6
Hello, World!

TIP: Before moving on to more complex examples make sure you know about the Differences Between Interactive and Scripted mongo

Let's assume we are interested in the creation frequency of our collection monsters.

We'll use a MongoDB Aggregation Pipeline to generate a CSV data file.

var result = db.monsters.aggregate(
  [
    ...
  ])
result.forEach(function(it) {
  print(it.result)
});

In a first aggregation step we $project the field createdAt of every document to the corresponding year ($year) and month ($month):

{
  $project: {
    month: { $month: "$createdAt" },
    year: { $year: "$createdAt" }
  }
}

Afterwards we $group the data per month and create a $sum with:

{
  $group: {
    _id: { year: "$year", month: "$month" }, count: { $sum: 1 }
  }
}

This intermediate result will most probably contain an unsorted index. Thus we add a $sort step:

{
  $sort: {
    "_id.year": 1,
    "_id.month": 1
  }
}

Last aggregation step is to convert the data to a CSV:

{
  $project: {
    result: { $concat: [
        { "$substr": [ "$_id.year", 0, 4 ] },
        "-",
        { "$cond": [
                { "$lte": [ "$_id.month", 9 ] },
                { "$concat": [ "0", { "$substr": [ "$_id.month", 0, 1 ] } ]},
                { "$substr": [ "$_id.month", 0, 2 ]}
        ]},
        ";",
        { "$substr": [ "$count", 0, 4 ] }
    ]}
  }
}

Looks a bit overcomplicated - maybe we come up with a shorter toString projection in the future...

Anyway running the overall script the result should look something like:

MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.6
2017-04;17
2017-05;132
2017-06;67
2017-07;193

TIP: To omit the first lines you can append a | tail -n +4 to your command. (Output will start with the fourth line.)

Show Comments