Optimal index for groupby and having n1ql query

SQL++
, ,
1

In our bucket we maintain following documents

{
	"accountID": "account1",
	"type": "recorded",
	"canonicalID":"cid1",	
	"resourceID": "rsid1",
	"amount":1000
}
{
	"accountID": "account1",
	"type": "recorded",
	"canonicalID":"cid1",	
	"resourceID": "rsid2",
	"amount":980
}
{
	"accountID": "account1",
	"canonicalID":"cid2",
	"type": "recorded",
	"resourceID": "rsid3",
	"amount":500
}

{
	"accountID": "account1",
	"canonicalID":"cid3",
	"type": "booked",
	"resourceID": "rsid4",
	"amount":800
}

n1ql query below calculates the sum of “amount” attributes for each distinct canonicalID where accountID="account1" and type="recorded". If there are multiple documents with the same canonicalID then for sum aggregation we consider document with the lowest amount value, i.e for the data above it would be 980 + 500 = 1480

SELECT SUM(amount)
	FROM bucket
WHERE  
	accountID="account1",
        type = "recorded"
GROUP BY canonicalID
HAVING MIN(amount);

What would be the optimal index to make this query efficient

2

Have you tried running your statement through: https://index-advisor.couchbase.com/indexadvisor ?

HTH.

3

CREATE INDEX ix1 ON bucket(accountID, canonicalID, amount) WHERE type = "recorded";