Select and group by "month"

Mobile Couchbase Lite
1

Hi

I’m trying to present some “overview” data to the mobile users. So ideally, I would like to “transform” a N1QL query like this to CB Lite:

select sum(`count`) count,SUBSTR(date, 5,2) month from data 
where type="Catch" and userkey="2124DEFEC111BA8FC1257ED20034B387"
group by SUBSTR(date, 5,2)

But I’ve not been able to find a proper way to extract the month…

Any suggestions as to how to do this? - apart of “brute force” (retrieving all records and manually build the resultset)…

2

If it is N1QL

SELECT sum(`count`)  AS count, SUBSTR(date, 5,2) AS month 
FROM  data 
WHERE type="Catch" AND  userkey="2124DEFEC111BA8FC1257ED20034B387"
GROUP BY  SUBSTR(date, 5,2);

CREATE INDEX ix1 ON data (userkey, SUBSTR(date, 5,2), `count`)  WHERE type="Catch" ;


OR 

SELECT sum(`count`)  AS count, DATE_PART_STR(date,"month") AS month 
FROM  data 
WHERE type="Catch" AND  userkey="2124DEFEC111BA8FC1257ED20034B387"
GROUP BY   DATE_PART_STR(date,"month");

CREATE INDEX ix2 ON data (userkey, DATE_PART_STR(date,"month"), `count`)  WHERE type="Catch" ;
3

Oh, I need the same query in CBLite - where I cannot use N1QL.

I know how to do it in N1QL… :slight_smile:

4

Got it. I realized after i posted response.

5

Unfortunately CBL doesn’t have the SUBSTR operator. Nor do we have the N1QL date/time function that would extract the month. (Attn @pasin, who keeps track of API requests.)

Yeah, I think you’ll need brute force for this. Either that or restructure your data to store the month as a separate property…

6

Okay, thanks @jens :+1: