CouchDB Count Reduce with timestamp filtering

Question

Let's say I have documents like so:

{ 
  _id: "a98798978s978dd98d", 
  type: "signature", 
  uid: "u12345", 
  category: "cat_1", 
  timestamp: UNIX_TIMESTAMP 
}

My goal is to be able to count all signature's created by a certain uid but being able to filter by timestamp

Thanks to Alexis, I've gotten to this far with a reduce _count function:

function (doc) {
  if (doc.type === "signature") {
    emit([doc.uid, doc.timestamp], 1);
  }
}

With the following queries:

start_key=[null,lowerTimestamp]
end_key=[{},higherTimestamp]
reduce=true
group_level=1

Response:

{
  "rows": [
    {
      "key": [ "u11111" ],
      "value": 3
    },
    {
      "key": [ "u12345" ],
      "value": 26
    }
  ]
}

It counts the uid correctly but the filter doesn't work properly. At first I thought it might be a CouchDB 2.2 bug, but I tried on Cloudant and I got the same response.

Does anyone have any ideas on how I could get this to work with being ale to filter timestamps?

Glynn Bird Glynn Bird · Accepted Answer · 2019-01-16T08:59:38

When using compound keys in MapReduce (i.e. the key is an array of things), you cannot query a range of keys with a "leading" array element missing. i.e. you can query a range of uuids and get the results ordered by timestamp, but your use-case is the other way round - you want to query uuids by time.

I'd be tempted to put time first in the array, but unix timestamps are not so good for grouping ;). I don't known the ins and outs of your application but if you were to index a date instead of a timestamp like so:

function (doc) {
  if (doc.type === "signature") {
    var date = new Date(doc.timestamp)
    var datestr = date.toISOString().split('T')[0]
    emit([datestr, doc.uuid], 1);
  }
}

This would allow you to query a range of dates (to the resolution of a whole day):

?startkey=["2018-01-01"]&endkey=["2018-02-01"]&group_level=2

albeit with your uuids grouped by day.

CouchDB Count Reduce with timestamp filtering

1 Answers