GROUP BY with WHERE clause using Views

I have documents like:


I need to count the daily installs for given set of countries. In SQL it would be something like:

select day_utc, count(*) as count from table where event="install" and country_code in ["US", "CA"] and day_utc>=start_day and day_utc<=end_day group by day_utc order by day_utc

I realized recently that I would not be able to use N1QL to do this without the queries running very slow.

So I thought I might be able to do this using Views. I’ve created a view which emits([day_utc, country_code]) for the map function and uses the predefined _count for the reduce function.

I would like to specify startkey, endkey and keys=[[null,“US”],[null,“CA”]], but as mentioned in the documentation I cannot specify the second key field without specifying the first.

Is there any way I will be able to do this using Couchbase?

1 Like

with views, the order of the keys are important when grouping. you can reorder your view key to emit country_code first and you won’t need the leading nulls in that case.

Thanks @cihangirb, I had considered changing the order to have the country_code emitted first, but then I would not be able to use startkey and endkey to select the date range.