Converting N1Ql query having "where" clause with "IN" parameters into mapreduce views

SQL++
,
1

I have a plain select query for which mapreduce view is already present.

Query:

select count(*) from my-bucket where type = 'Order' and status = 'CREATED' and timestamp > MILLIS("2016-05-15T03:59:00Z") and timestamp <= MILLIS("2017-05-15T03:59:00Z")

view:

function (doc, meta) {
	if (doc._class == "com.myclass.Order"){
		emit([doc.type,doc.status, doc.timestamp], null);
	}	
}

query:

Start key : ["Order","CREATED",1535605294320]
End key : ["Order","CREATED",1535605594320]

Requirement: Now, we would want this view to support a query having “IN” clause on “status” parameter. Also, we would like to add additional parameters supporting “IN” parameters. Sample N1Ql would be like below.

select count(*) from my-bucket where type = 'Order' and orderType IN ["TYPE-A","TYPE-B","TYPE-C"]and status IN ['CREATED',""READY,"CANCELLED"] and timestamp > MILLIS("2016-05-15T03:59:00Z") and timestamp <= MILLIS("2017-05-15T03:59:00Z")

How to write a query on view to accomplish this? Only solution comes to my mind is to fire multiple (lets says x) queries on views

where x = m1*m2*....*mn 
m1=number of paremeters in first IN clause
n=number of IN clauses.

Is there any better solution like executing this query in batch or a single mapreduce query?

2

I am not view expert, I think only option you have write IN clause transform into OR and call view multiple times and sum it. cc @jeelan.poola

Why you are not using N1QL using following index

CREATE INDEX ix1 ON `my-bucket`(orderType,status,timestamp) 
WHERE type = "Order";

If you are using EE version checkout SQL Group by Index | Aggregate Index SQL | Couchbase
Couchbase GSI Index partitioning - The Couchbase Blog

3

Yes. That is my back up plan. Calling the view multiple times is the only option i can think off. Anf fyi, we are using CE version, not EE.