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.

score 1 · Answer 1 · answered Jun 08 '15 at 03:32

1

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.

answered Jun 08 '15 at 03:32

Akshat Jiwan Sharma

15,430
13
50
60

All true, but I don't think CouchDB can select a date range from the second key. I somehow need the excluded dates not to show up at all before the reduce by type. – Pete Jun 08 '15 at 03:43
Do you mean you want to filter based on type and date range but group by type. If so you can exclude keys by start and endkey parameters. Like you did in your question. `startkey=["type1",start_date_range]&endkey=["type1",end_date_range]&reduce=true&group_level=1`. Or are you looking for something else? – Akshat Jiwan Sharma Jun 08 '15 at 04:37
Yes, but for all types. I understand why CouchDB cannot do this, because it only selects contiguous ranges. See http://markmail.org/thread/4gd3734dencrqwoj from the couch-db users mailing list. That guy implies you can do it with a "second view". – Pete Jun 08 '15 at 04:55
I am upvoting this because it's a good, clear illustration of the problems one runs into when trying to group by type over a range on an index. – Pete Jun 08 '15 at 16:18
Thanks. Glad you found it useful :) – Akshat Jiwan Sharma Jun 08 '15 at 17:01

score 1 · Accepted Answer · edited May 23 '17 at 10:26

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.

I forgot about lists! If you are using list functions then there is no need to emit such large values. Just use an `include_docs=true` parameter. That is an emit like `emit(doc.date)` queried with `include_docs=true` will get you the same result with the added advantage of smaller view indices. The document will be accessible in `row[index].doc` . Use `log()` to print stuff on console to help you debug. Hope it helps :) — Akshat Jiwan Sharma, Jun 08 '15 at 17:00

CouchDB sum by date range and type

2 Answers2