GROUP BY with WHERE clause using Views

SQL++
1

I have documents like:

{
  day_utc:16650,
  country_code:"US",
  event:"install"
}

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. https://www.couchbase.com/forums/t/use-index-does-not-find-the-index

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
2

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.

3

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.