CosmosDB get the SUM of a field within an array

Question

We have an object in Azure CosmosDB such as the folowing:

{
"id": "2hgxw",
"invoices": [
        {
            "key": "123",
            "value": 1200
        }
    ],

}

We would like to create a filter such that we can select objects that have a balance or not but our code gives errors.

What we are trying is this:

SELECT c.id FROM pros c WHERE c.id='somevalue' AND SUM(c.invoices.value)<>0

Appreciated.

Answer 1

Several problems:

• First, you're referencing a property with the same name as a keyword (value) - this will throw an error. This needs to be in the format c["value"] (or rename the property)
• Second: you cannot pass array values the way you're doing it, to sum() - sum() works against a single property, across documents. Instead, you have to do a self-join. This example sums all of the value properties of the array, as a total (without any filtering - just showing how to work with array data and aggregations):

SELECT sum(i["value"]) as total
FROM c
join i in c.invoices

To filter on total value, then you have to get a bit fancier with your inner join and aliases. Something like this would work (I added invoice count just for fun), assuming two items in your array: one with value 1200 and one with value 1:

SELECT invoiceData.invoiceCount, invoiceData.invoiceTotal
FROM c
JOIN
   (SELECT COUNT(1) as invoiceCount,
    SUM(i["value"]) as invoiceTotal
    FROM i in c.invoices)
   AS invoiceData
where
   invoiceData.invoiceTotal <> 0

This would yield:

[
    {
        "invoiceCount": 2,
        "invoiceTotal": 1201
    }
]