SELECT COUNT(DISTINCT *) in CouchDB

Question

I have documents complying this format:

{
  "_id": "some_doc_id", 
  "user": "some_user_id", 
  "date": "2015-09-15",
  …
}

It's possible to have multiple documents with the same user. I'd like to count how many distinct users there are between two dates. E.g. Between '2015-09-15' and '2015-09-25', there was 745 different users.

In SQL, I would write this query:

SELECT COUNT(DISTINCT user)
FROM documents
WHERE date BETWEEN '2015-09-15' and '2015-09-25'

Thank you.

Dominic Barnes Dominic Barnes · Accepted Answer · 2016-02-08T06:53:33

I would use a map function like:

function (doc) {
  emit(doc.date, doc.user);
}

Which will emit documents sorted by date, with the user being the value. The reduce function will look like this:

function(keys, values, rereduce) {
  if (rereduce) {
    return values.reduce(function (acc, index) {
      return Object.keys(index).reduce(function (acc, user) {
        acc[user] = (acc[user] || 0) + index[user];
        return acc;
      }, acc);
    }, {});
  } else {
    return values.reduce(function (acc, user) {
      if (!(user in acc)) acc[user] = 0;
      acc[user]++;
      return acc;
    }, {});
  }
}

It's a custom reduce function, so bear with me for a brief explanation. The normal case (the 2nd branch, not the rereduce) basically counts up the values it finds. The result is an object like { some_user_id: 1 }.

The rereduce branch basically takes several of the reduced objects and merges them (and their counts) together into 1 reduced result. (you can read more about reduce and rereduce here)

From there, you can query your view with the following query params:

start_key="2015-09-15"
end_key="2015-09-25"

You'll end up with the same results as shown earlier. (ie: { some_user_id: 1 }) On your client, you can count up the keys in the resulting object to get an idea for how many unique users there are for any given date range.

SELECT COUNT(DISTINCT *) in CouchDB

1 Answers