Aggregate function with JavaScript query API in Azure Cosmos DB

Question

I'm working with CosmosDB stored procedure and user defined function.

I have to write a stored procedure that return the max of four fields of a collection.

In Sql I write:

    SQL MAX(A) AS MASSIMOA, MAX(B) AS MASSIMOB, .... FROM COLLECTION

I don't know how to write it using javascript into a stored procedure. Can anyone help me? Simone

Sample Data :

    {
        "messageUID": "EBBBrain-10608941722019-05-31T16:58:13+02:00",
        "deviceId": "EBBBrain-1060894172",
        "dateTimeDevice": "2019-05-31T14:58:13",
        "messageId": 38,
        "release": 104,
        "VIn": 23342,
        "VOut": 20513,
        "AIn": 53,
        "AOut": 40,
        "CosPhi": 42,
        "W": 53,
        "Var": 112,
        "VA": 124,
        "WhCnt": 361587,
        "VarhCnt": 749631,
        "TimeSlot": 0,
        "MeterTS": "2019-05-31 16:58:14",
        "Sampling": 60,
        "Wh": 3423,
        "Varh": 7105,
        "WSaved": 0,
        "EventProcessedUtcTime": "2019-05-31T14:58:15.3238226Z",
        "PartitionId": 1,
        "EventEnqueuedUtcTime": "2019-05-31T14:58:15.285Z",
        "IoTHub": {
            "MessageId": null,
            "CorrelationId": null,
            "ConnectionDeviceId": "Device",
            "ConnectionDeviceGenerationId": "636909297614425839",
            "EnqueuedTime": "2019-05-31T14:58:15.292Z",
            "StreamId": null
        },
        "id": "EBBBrain-1060894172",
        "_rid": "dEkOAONukREBAAAAAAAAAA==",
        "_self": "dbs/dEkOAA==/colls/dEkOAONukRE=/docs/dEkOAONukREBAAAAAAAAAA==/",
        "_etag": "\"2400a1a2-0000-0c00-0000-5cf1415c0000\"",
        "_attachments": "attachments/",
        "_ts": 1559314780
    }

and the sql statement based on the sample is :

    SELECT max(r.VIn) as maxNum FROM root r

Answer 1

Cosmos db supports native aggregate function as below:

So, you could use MAX in normal sql.

'SELECT max(r.num) as maxNum FROM root r'

In stored procedure, no more difference.

// SAMPLE STORED PROCEDURE
function sample() {
    var collection = getContext().getCollection();

    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT max(r.num) as maxNum FROM root r',
    function (err, feed, options) {
        if (err) throw err;

        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
        }
        else {
            var response = getContext().getResponse();
            response.setBody(feed);
        }
    });

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

Output:

---

Update Answer:

The issue is resulted from the size of data. The aggregate function in the cosmos db will across all the data if you do not set any filter in the query sql. That's a such huge workload and can't be finished by stored procedure directly. Stored procedure has execute limitation, please refer to this link:

So, my suggestion is using continuation token to doing the paging completion. You could set the maxItemCount param in the code,like below:

client.QueryDocuments(collection_link, query, {'maxItemCount':100})

Then use continuation token to get the max number of single page. Finally, compare those numbers to get the maximum. About usage of continuation token,you could refer to this thread: Document DB 5 secs stored procedure execution limit