Cosmos DB stored procedure group by example

groupByProcedure.js

/*
sampele data:

```
{ "id":"1","category":"00", "item":"item1" ,"price":300}
{ "id":"2","category":"00", "item":"item2" ,"price":400}
{ "id":"3","category":"01", "item":"item3" ,"price":500}
{ "id":"4","category":"01", "item":"item4" ,"price":600}
{ "id":"5","category":"02", "item":"item5" ,"price":700}
```

probrem:

`SELECT sum(c.price) FROM c` is OK.
 but,  `SELECT sum(c.price) FROM c group by c.category` is NG.

answer:

1. execute query each category
    `SELECT sum(c.price) FROM c where c.category == '00'`
    `SELECT sum(c.price) FROM c where c.category == '01'`
    `SELECT sum(c.price) FROM c where c.category == '02'`
   
    Each query is low RU/s. but API call increases.

2. make stored procedur. following is example.
   
*/
//
function sumByCategory(categories) { // catogories is ["00", "01", "02"].
    let context = getContext();
    let collection = context.getCollection();
    let response = context.getResponse();
    let result = {}
    categories.forEach(category => {

        collection.queryDocuments(collection.getSelfLink(),
         `select VALUE sum(c.price) from c where c.category = "${category}"`,
         {},
         (err, documents, responseOptions) => {
            result[category] = documents[0]

            // check all query results finished.
            if (Object.keys(result).length == categories.length) {
                response.setBody(result)
            }
         });
    });

}

groupByProcedureAlternative.js

function sumByCategory2() { 
    let response = getContext().getResponse();
    // this needs high RU/s if collection has many document.
    // beacuse this procedure loads all documents. 
    
    __.chain().map(doc => doc)
        .value((err, res, opt) => { 
            // edit result in value method callback.
            // res is collection result array.
            let result = {}
            res.forEach(doc => {
                if (!(doc.category in result)) {
                    result[doc.category] = 0
                }
                
                result[doc.category] += doc.price
            });
                
            response.setBody(result) // {"00":700, "01":1100, "02":700} 
            
        });
}