CouchDB View - filter keys before grouping

Question

I have a CouchDB database which has documents with the following format:

{ createdBy: 'userId', at: 123456, type: 'action_type' }

I want to write a view that will give me how many actions of each type were created by which user. I was able to do that creating a view that does this:

 emit([doc.createdBy, doc.type, doc.at], 1);

With the reduce function "sum" and consuming the view in this way:

/_design/userActionsDoc/_view/userActions?group_level=2

this returns a result with rows just in the way I want:

"rows":[ {"key":["userId","ACTION_1"],"value":20}, ...

the problem is that now I want to filter the results for a given time period. So I want to have the exact same information but only considering actions which happened within a given time period.

I can filter the documents by "at" if I emit the fields in a different order.

?group_level=3&startkey=[149328316160]&endkey=[1493283161647,{},{}]

emit([doc.at, doc.type, doc.createdBy], 1);

but then I won't get the results grouped by userId and actionType. Is there a way to have both? Maybe writing my own reduce function?

Justin Heath Justin Heath · Accepted Answer · 2017-05-19T19:42:50

I feel your pain. I have done two different things in the past to attempt to solve similar issues.

The first pattern is a pain and may work great or may not work at all. I've experienced both. Your map function looks something like this:

function(doc) {
  var obj = {};
  obj[doc.createdBy] = {};
  obj[doc.createdBy][doc.type] = 1;

  emit(doc.at, obj);
  // Ignore this for now
  // emit(doc.at, JSON.stringify(obj));
}

Then your reduce function looks like this:

function(key, values, rereduce) {
  var output = {};
  values.forEach(function(v) {
    // Ignore this for now
    // v = JSON.parse(v);
    for (var user in v) {
      for (var action in v[user]) {
        output[user][action] = (output[user][action] || 0) + v[user][action];
      }
    }
  });
  return output;
  // Ignore this for now
  // return JSON.stringify(output);
}

With large datasets, this usually results in a couch error stating that your reduce function is not shrinking fast enough. In that case, you may be able to stringify/parse the objects as shown in the "ignore" comments in the code.

The reasoning behind this is that couchdb ultimately wants you to output a simple object like a string or integer in a reduce function. In my experience, it doesn't seem to matter that the string gets longer, as long as it remains a string. If you output an object, at some point the function errors because you have added too many props to that object.

The second pattern is potentially better, but requires that your time periods are "defined" ahead of time. If your time period requirements can be locked down to a specific year, specific month, day, quarter, etc. You just emit multiple times in your map function. Below I assume the at property is epoch milliseconds, or at least something that the date constructor can accurately parse.

function(doc) {
  var time_key;
  var my_date = new Date(doc.at);

  //// Used for filtering results in a given year 
  //// e.g. startkey=["2017"]&endkey=["2017",{}]
  time_key = my_date.toISOString().substr(0,4);
  emit([time_key, doc.createdBy, doc.type], 1);

  //// Used for filtering results in a given month
  //// e.g. startkey=["2017-01"]&endkey=["2017-01",{}]
  time_key = my_date.toISOString().substr(0,7);
  emit([time_key, doc.createdBy, doc.type], 1);

  //// Used for filtering results in a given quarter
  //// e.g. startkey=["2017Q1"]&endkey=["2017Q1",{}]
  time_key = my_date.toISOString().substr(0,4) + 'Q' + Math.floor(my_date.getMonth()/3).toString();
  emit([time_key, doc.createdBy, doc.type], 1);
}

Then, your reduce function is the same as in your original. Essentially you're just trying to define a constant value for the first item in your key that corresponds to a defined time period. Works well for business reporting, but not so much for allowing for flexible time periods.

CouchDB View - filter keys before grouping

1 Answers