如何在Azure Cosmos DB中使用SUM聚合函数

huangapple go评论63阅读模式
英文:

How to use SUM aggragate functions in Azure Cosmos DB

问题

根据官方文档的描述,Cosmos DB SQL API支持聚合函数。但是,我找不到任何针对项目多层文档结构执行聚合的合适查询示例。
这是我的Cosmos DB容器项目的结构。我正在使用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
}

我想使用SQL查询获取invoiceDetails.lineTotal的总和。非常感谢您的回答。

英文:

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

答案1

得分: 2

使用SELECT...FROM...IN可以让您仅处理文档的一部分,即invoiceDetails数组。

因此,这个查询:

SELECT *
FROM a in c.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
    }
]

然后,您可以使用SUM对数组中的项目进行求和。

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

并得到:

[
    {
        "SumLineTotal": 1000
    }
]
英文:

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
    }
]

huangapple
  • 本文由 发表于 2020年1月7日 01:24:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/59616403.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定