DEV Community

Cover image for Filter CouchDB query results with arbitrary JavaScript - like SQL WHERE...
Jesper Høy
Jesper Høy

Posted on • Originally published at jesperhoy.dev

Filter CouchDB query results with arbitrary JavaScript - like SQL WHERE...

#couchdb #javascript

CouchDB has a "List function" feature which allows you to transform query results.

This can be used to filter results and/or generate HTML, XML, JSON, etc. - in CouchDB - before it is returned to the client.

A List function (JavaScript) needs to be stored in a design document in the same database as the data to be queried. So this JavaScript code is going to be static.

However, when executing a List function, it is possible to provide various parameters to it via URL query parameters (available as members of the Request.query object).

By sending JavaScript code as one such parameters, it is actually possible to run arbitrary JavaScript - dynamically generated client side(*) and potentially unique for each query - as part of a List function.
And so, a single List function can be used to execute any number of client defined(*) filters/transforms.

This is very similar to SQL WHERE functionality - only using JavaScript instead of SQL.

The following is the JavaScript code for a List function which takes 3 custom URL query parameters; "js_code" as the filter code to execute for each result row, "js_skip" as the number of initial rows to skip, and "js_limit" to limit the number of rows returned:

function(head, req){
  let filter = new Function("_r", "return (" + req.query.js_code + ")(_r);");
  let Skip = parseInt(req.query.js_skip);
  let Limit = parseInt(req.query.js_limit);
  start({'headers': {'Content-Type': 'application/json'}});
  let resp = head;
  resp.rows=[];
  let row;
  while (row = getRow()) {
    if (filter(row)) {
      if (Skip > 0) {
        Skip -= 1;
      } else {
        resp.rows.push(row);
        if (Limit>0 && resp.rows.length >= Limit) break;
      }
    }
  }
  return JSON.stringify(resp);
}
Enter fullscreen mode Exit fullscreen mode

The code above would need to be included in a design document under lists / jsfilter.

I now add this exact List function to most of my CouchDB design documents, so that I always have this functionality readily available.

The JavaScript code that you send in the js_code parameters would look somthing like this:

function(row) {
  if(row.OrderNumber != 3) return false;
  return row.Color == "blue";  
}
Enter fullscreen mode Exit fullscreen mode

Or, the short version:

row => row.OrderNumber!=3 ? false : row.Color=="blue";
Enter fullscreen mode Exit fullscreen mode

(*) By "client side" / "client defined", I mean whatever code is sending the query to CouchDB. This would typically be code running on a server, like web-server server side script. You obviously don't want to give end-users direct access to this.

NOTE: The CouchDB documentation states that List functions are deprecated and will be removed in "CouchDB v. 4".
"V. 4" was a, now abandoned, effort to replace the underlying storage engine for CouchDB.
Since that "v. 4" will never be, and the current v. 3 is very actively maintained, I think (and hope) List functions will be around for the foreseeable future.

Top comments (0)