While re-writing a couple SQL statements into CouchDB we got stuck when we wanted to do a SELECT MAX(...), id ... GROUP BY id in CouchDB.

MySQL

Imagine the following SQL table with data:

In order to get the latest deploy for each project, I’d issue:

Simple. But what do you do in CouchDB?

CouchDB

My documents look like this:

So, after more than a couple hours trying to wrap our heads around map-reduce in CouchDB, it’s working.

Here’s the view’s map function:

This produces nice key value pairs — in fact, multiple — for each project.

And because the map-function returns multiple, we need to reduce our set.

So here is the reduce:

The little .replace(/ /gi,‘T’) took especially long to figure out. Special thanks to Cloudant’s señor JS-date-engineer Adam Kocoloski for helping out. ;-)

Step by step

  • iterate over values
  • fix each value (add the “T”) to make Spidermonkey comply
  • parse, and compare
  • return the “latest” in the end

A note of advice: To save yourself some trouble, install a local copy of Spidermonkey and test your view code in there and not in your browser.

Open the view in your browser: http://localhost/db/_design/deploys/_view/last_deploys?group=true

Yay, works!

FIN

That’s all for today.