Cloudant: Filtering and aggregation at multiple levels

Question

I have a documents collection in cloudant

{
    "id" : String

    "state": String
    "city": String
    "zipcode": int

    "productType": String
    "make": String
    "model": String
    "sku": String

    "usedOn": Date
    "energyUSed": int
    "durationUsed": int
}

I would like to filter on multiple criterias - state, city, zipcode, make, model, sku etc and aggregate energyUsed and durationUsed

I looked at querying cloudant using map/reduce, query indexes and _find target. However none of these is flexible enough to filter and aggregate at multiple levels.

For example, I would like to filter based on make and model, and aggregate at state or city level. In another case, I would like to filter based on state, city, make and model and aggregate at month level (using usedOn).

Any help is much appreciated.

seb seb · Accepted Answer · 2016-06-02T21:15:02

You can achieve quite a lot by defining the right views (map/reduce functions).

Let's say you want to find energy used and filter by make model and aggregate at state or city, you could define a following map:

var makeModelKey = doc.make + '_' + doc.model;
emit([makeModelKey, doc.state, doc.city], doc.energyUsed);

The reduce function is simply the built in _sum one.

Now querying by a specific make and model and adjusting the group level for reduce, you will be able to get energy used per make_model, then split by state and then further split by city.

If you only want to group by state and city, simply define a different view without the make_model part of the key.

You cannot have one view to achieve all, couch dB is not a RDB. You need a different approach.

Your other queries may require defining slightly different map/reduce functions but all of that is achievable.

One thing that was hard for me initially, was to break out of the 'relational' way of thinking - switching from SQL world to doc dbs and map/reduce world requires a different approach to data and query modelling.

Cloudant: Filtering and aggregation at multiple levels

1 Answers