CouchDB sum by date range and type

Question

Simply put I want to _sum totals over a date range grouped by type. The original docs in the db are each for a single date, containing data by type. (For example, each doc has total apples, oranges, and pears picked on a date. We want to query for total apples, oranges, and pears picked over a date range.)

The "myview" map sends out

emit([date, type], values_array);

which I can query for date ranges as

..._view/myview?group=true&group_level=2&startkey=[20150501]&endkey=[20150530,{}]

and there lies the problem. It can give a sum for each date at group_level=1, but I want to forget about the date at that point. I want to somehow re-key on type and then sum.

I think I need two views in succession for this, but not sure how to do that.

Answer 1

I accomplished this using a list function on the view. Here's the overview:

• The View handles selecting the date range (key by date and use .../myview?startkey=20150101&endkey=20150130
• The List has some Javascript which groups by type. (As a bonus, you can also sort).

My List function looks like this (based on this Q&A about grouping a javascript array by type):

function (head, req){
         var row;
         var rows = [];
         while(row = getRow()){
                rows.push({
                    "type": row.value.type,
                    "value1": row.value.value1,
                    "value2": row.value.value2      
                });
         };


        var result = rows.reduce(function(res, obj) {
                if (!(obj.type in res)){
                    res.__array.push(res[obj.type] = obj);
                } else {
                    res[obj.symbol].value1 += obj.value1;
                    res[obj.symbol].value2 += obj.value2;
                }
                return res; 
                }, 
                {__array:[]}).__array;

        send(toJSON(result));
    }

The prior View should emit the date as the key, and a javascript object as the value. In this example, a row of the view should look like: "key":20150101, "value":{"type":"apple", "value1":28, "value2":0}. If you are new to Couch, here is how you write your map function (and don't use a reduce however tempted you may be to _sum):

function(doc){
    if (doc.type === "mydoctype"){
        // build array for the day
        var items = [];
        doc.items.forEach(function(item){
            items.push({
                    'type': item.type,
                    'value1': +item.value1,
                    'value2': +item.value2
                   });
             });

        items.forEach(function(item){
            // convert text date "yyyy-mm-dd"
            var x = doc.date.split('-');
            // to numerical date YYYYMMDD
            newformatdate = +(x[0]+x[1]+x[2]);

            emit(newformatdate, item);
        });
    }
}

Lastly, your query would look like this:

http://localhost:5984/dbname/_design/ddocname/_list/mylistname/myviewname?startkey=20150501&endkey=20150510

I am somewhat new to both Javascript and Couch, so feel free to take a whack at this code.

Answer 2

Try this map function

emit([type,date], values_array);

The order of the keys is important. If you want to group your query results then you want to emit your keys in a "least changing to most changing" order. That is emit the key in the document which will change the least for a group first. Then the key which will change more than the previous one and so on. For example the emit function above should return an output as

["type1",20150501],val of the keys
["type1",20150502],val of the keys
["type1",20150503],val of the keys
["type2",20150502],val of the keys

Note that "type1" is same across three results and the date changes the most. Now if you do ?group_level=1 on the view you will get a result like

["type1"],val of the keys

That is all the keys grouped by "type1". If you do ?group_level=2 you will get all the keys grouped by "type1" and the date key. Which means that if both the first and the second keys are equal they will be grouped together.

Grouping in couchdb occurs from left to right. First leftmost keys are checked to see if they are equal, then the next keys are checked and so on. All the equal keys are grouped together.