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