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

https://learn.microsoft.com/en-us/azure/cosmos-db/how-to-write-stored-procedures-triggers-udfs — Mani, Jul 09 '19 at 21:06

score 0 · Accepted Answer · edited Jul 16 '19 at 22:34

0

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

edited Jul 16 '19 at 22:34

halfer

19,824
17
99
186

answered Jul 10 '19 at 06:19

Jay Gong

23,163
2
27
32

I try your code in my enviroment putting my field in the select clause. I have a problem . The result is [ {} ] What is the problem? Thanks. Simone – Simone Spagna Jul 11 '19 at 07:59
@SimoneSpagna Never mind,so what's your data and any differences between ours' stored procedure code? – Jay Gong Jul 11 '19 at 08:37
See my reply. Thanks a lot. Simone – Simone Spagna Jul 11 '19 at 10:15
@SimoneSpagna Sorry for missing your update reply. So, even you executed `select * from c` also get empty result. Perhaps you should execute the sql in the azure portal directly first. Have you jumped into an mismatch collection or whether the collection has items indeed? please check it again,waiting for you reply! – Jay Gong Jul 12 '19 at 02:09
If j execute select * from c from azure protal i obtain the first 100 record of the collection .................. – Simone Spagna Jul 12 '19 at 05:15
@SimoneSpagna Strangely,could you please share your sample data? Some screenshot is fine too,please. Remember,don't leave that in an new answer, please edit your question. – Jay Gong Jul 12 '19 at 06:22
Posted one record. My collection have more than 20000 records. Thanks. – Simone Spagna Jul 12 '19 at 07:12
@SimoneSpagna Sorry for the late,please see my update answer. – Jay Gong Jul 15 '19 at 06:33
@SimoneSpagna Hi,any progress? – Jay Gong Jul 16 '19 at 01:36
It seems correlated to this topic https://stackoverflow.com/questions/48798523/azure-cosmos-db-asking-for-partition-key-for-stored-procedure – Simone Spagna Jul 16 '19 at 07:05
@SimoneSpagna well,i think not.just share the same document,but indicate different issue. – Jay Gong Jul 16 '19 at 07:07
@SimoneSpagna You could test maxItemCount param, see any difference. – Jay Gong Jul 16 '19 at 07:09

Aggregate function with JavaScript query API in Azure Cosmos DB

1 Answers1