How to create reports in MongoDB with just some Javascript

I didn't appreciate what I had at my disposal when first working with MongoDB.

It frustrated me by not being an SQL database, by not having joins and transactions. I came to it with the SQL mindset and it did not comply with my expectations.

It took me a long time working on Express apps in Node.js until I realized, when working on a support ticket, that I could use Javascript directly in Mongo, without having to write a Node app.

When would I take advantage of that?

Suppose the customer wants to receive a one-off report of some kind. If you have a Postgres database, you write some SQL. With Mongo, you write some Javascript.

Let's consider a simple example. Your marketing wants to receive a list of customers who have placed something in their cart but did not finish the checkout process.

Let's say you have two collections: customers and orders. The report ought to contain the user's name, e-mail, and the name of the most valuable item in the shopping cart.

Furthermore, assume that the items placed into the shopping cart are stored in the orders collection as sub-documents.

This is how I would go about it, roughly:

let unfinished = [];
let reportingPeriodStart = new Date("2020-01-01");
let orders = db.getCollection('orders').find({
  createdAt: { $gte: reportingPeriodStart },
  isPurchased: false
}).toArray();
for (let order of orders) {
  let customer = db.getCollection("customers").findOne({
    _id: order._customer
  });
  unfinished.push({
    name: user.fullName,
    email: user.email,
    item: order.items.reduce(
      (best, current) =>
        current.price > best.price
          ? current
          : best,
      { price: 0 }
      )
  })
}
print("Name;Email;Item")
print(unfinished.map(o => `"${o.name}";"${o.email}";${o.item.name}`).join('\n'))

You'll save this file somewhere, and run the mongo client to receive your report:

mongo -u username -p password --quiet mongodb://localhost:27017 yourfile.js

(COMPLAINT: even with the --quiet flag, mongo still prints out useless status information to STDOUT, necesistating further processing with your favorite Linux commandline tool)

As you can see, you can get around the lack of SQL by writing simple procedural code that can find, filter, sort, and otherwise manipulate the results.

Personally, I would still very much prefer to write SQL. It's declarative and you could get the same result as from the above example with less amount of code.

MongoDB does not have SQL but it has Javascript. It's not the next-best-thing to SQL but it's a lot better than nothing at all. I don't have to write an extra app to get my data out, and that is very good indeed.

Published on 01 Feb 2020

What do you think? Sound off in the comments!