CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
Rama
Rama

Reputation: 307

How to use SUM aggragate functions in Azure Cosmos DB

As described in the official documentation, Cosmos Db SQL API support for aggregate functions. However, I could not find any decent query example which performs aggregate against items' multi-level document structure. This is the structure of my Cosmos DB container items. I am using SQL API

{
"id": "1",
"invoiceNo": "INV0001",
"date": "2019/12/20",
"userId": "cashier1",
"invoiceDetails": [
    {
        "itemName": "Item 1",
        "qty": 1,
        "unitPrice": 100,
        "lineTotal": 100
    },
    {
        "itemName": "Item 2",
        "qty": 3,
        "unitPrice": 200,
        "lineTotal": 600
    },
    {
        "itemName": "Item 6",
        "qty": 1,
        "unitPrice": 300,
        "lineTotal": 300
    }
],
"_rid": "h9Q6AKtS7i0BAAAAAAAAAA==",
"_self": "dbs/h9Q6AA==/colls/h9Q6AKtS7i0=/docs/h9Q6AKtS7i0BAAAAAAAAAA==/",
"_etag": "\"1500611a-0000-1800-0000-5e00fbd40000\"",
"_attachments": "attachments/",
"_ts": 1577122772

}

I want to get the sum of invoiceDetails.lineTotal using a SQL query. Your answers are highly appreciated

Upvotes: 1

Views: 3382

Answers (1)

Paul
Paul

Reputation: 637

Using SELECT...FROM...IN like this allows you to work with only part of the document, in this case the invoiceDetails array.

So this query:

SELECT *
FROM a in c.invoiceDetails

Produces:

[
    {
        "itemName": "Item 1",
        "qty": 1,
        "unitPrice": 100,
        "lineTotal": 100
    },
    {
        "itemName": "Item 2",
        "qty": 3,
        "unitPrice": 200,
        "lineTotal": 600
    },
    {
        "itemName": "Item 6",
        "qty": 1,
        "unitPrice": 300,
        "lineTotal": 300
    }
]

You can then use SUM to sum an item from the array.

SELECT SUM(a.lineTotal) AS SumLineTotal
FROM a in c.invoiceDetails

And get:

[
    {
        "SumLineTotal": 1000
    }
]

Upvotes: 2

Related Questions