Select distinct count cloudant/couchdb

Question

I am starting a project using Cloudant. It's a simple system for logging, so I can track the usage of my apps.

My documents looks like this:

{
app:'name of the app',
type:'page view | login | etc..',
owner:'email_of_the_user',
device: 'iphone | android | etc..',
date: 'yyyy-mm-dd'
}

I've tried to do some map reducing and faceted searches, but couldn't find so far the result for what I want.

I want to count the number of distinct documents grouped by same owner, date (yyyy-mm-dd), and app.

[For example, if a the same guy logs in the app twice or 20 times in the same date, it will be counted only once. I want to count how many single users used an app each day, no matter what's the type of the log, or the device he used.]

If it was SQL, assuming that each key of the document is a column, I would query something like this:

SELECT app, date, count(*) FROM LOGS group by date, owner, app

ant the result would be something like:

'App1', '2015-06-01', 200
'App1', '2015-06-02', 232
'App2', '2015-06-01', 142
'App2', '2015-06-02', 120

How can I get the same result using Cloudant/CouchDB?

Answer 1

You can do this using design documents, as Cesar mentioned. A concrete example would be to create a view where your map function emits the field on where you want to group on, such as:

function(doc) {
  emit(doc.email, 1);
}

Then, you select your desired reduce function (such as _count). When viewing this on Cloudant dashboard, make sure you select Reduce as part of the query options. When accessing the view via URL you need to pass the appropriate parameters (reduce=true&group=true).

The documentation on Views here is pretty thorough: https://docs.cloudant.com/creating_views.html

Answer 2

For what you need there is a feature on couldant/couchdb called design document. You can check their documentation for this feature for details or this guide: http://guide.couchdb.org/draft/design.html

Cloudant documentation: https://docs.cloudant.com/design_documents.html Design documents are similar views on the SQL world.

Regards,

Answer 3

We were able to do this in our project using the Cloudant Java API...

https://github.com/cloudant/java-cloudant

You should be able to get this sort of result by creating a view that has a map function like this...

function(doc) {
    emit([doc.app, doc.date, doc.owner], 1);
}

The reduce function should look like this:

function(keys, values, rereduce){
    if (rereduce){
        return sum(values);
    } else {
        return sum(values);
    }
}

Then we used the following query to get the data we wanted.

Database db = ....
db.view(viewName).startKey(startKeys).endKey(endKeys)
            .group(true).includeDocs(false).query(castClass)

We supplied the view name and some start and end keys (since we emitted a compound key and we needed to supply a filter) and then used the group method to get the data back as you need it.

Revised..

With this new emit key in the map function you should get results like this:

{[
{[app1, 2015,06,28, john@somewhere.net], 12}, <- john visited 12 times     on that day...
{[app1, 2015,06,29, john@somewhere.net], 10},
{[app1, 2015,06,28, ann@somewhere.net], 1}
]}

If you use good start and end keys, the amount of records you're querying will stay small and the number of records you get back is the unique visitors you are seeking. Note that in this scenario you are getting back a bit more than you want, but it does work.