英文:
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
}
]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论