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

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

How to use SUM aggragate functions in Azure Cosmos DB

问题

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

  1. {
  2.     "id": "1",
  3.     "invoiceNo": "INV0001",
  4.     "date": "2019/12/20",
  5.     "userId": "cashier1",
  6.     "invoiceDetails": [
  7.         {
  8.             "itemName": "Item 1",
  9.             "qty": 1,
  10.             "unitPrice": 100,
  11.             "lineTotal": 100
  12.         },
  13.         {
  14.             "itemName": "Item 2",
  15.             "qty": 3,
  16.             "unitPrice": 200,
  17.             "lineTotal": 600
  18.         },
  19.         {
  20.             "itemName": "Item 6",
  21.             "qty": 1,
  22.             "unitPrice": 300,
  23.             "lineTotal": 300
  24.         }
  25.     ],
  26.     "_rid": "h9Q6AKtS7i0BAAAAAAAAAA==",
  27.     "_self": "dbs/h9Q6AA==/colls/h9Q6AKtS7i0=/docs/h9Q6AKtS7i0BAAAAAAAAAA==/",
  28.     "_etag": "\"1500611a-0000-1800-0000-5e00fbd40000\"",
  29.     "_attachments": "attachments/",
  30.     "_ts": 1577122772
  31. }

我想使用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

  1. {
  2. "id": "1",
  3. "invoiceNo": "INV0001",
  4. "date": "2019/12/20",
  5. "userId": "cashier1",
  6. "invoiceDetails": [
  7.     {
  8.         "itemName": "Item 1",
  9.         "qty": 1,
  10.         "unitPrice": 100,
  11.         "lineTotal": 100
  12.     },
  13.     {
  14.         "itemName": "Item 2",
  15.         "qty": 3,
  16.         "unitPrice": 200,
  17.         "lineTotal": 600
  18.     },
  19.     {
  20.         "itemName": "Item 6",
  21.         "qty": 1,
  22.         "unitPrice": 300,
  23.         "lineTotal": 300
  24.     }
  25. ],
  26. "_rid": "h9Q6AKtS7i0BAAAAAAAAAA==",
  27. "_self": "dbs/h9Q6AA==/colls/h9Q6AKtS7i0=/docs/h9Q6AKtS7i0BAAAAAAAAAA==/",
  28. "_etag": "\"1500611a-0000-1800-0000-5e00fbd40000\"",
  29. "_attachments": "attachments/",
  30. "_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数组。

因此，这个查询：

  1. SELECT *
  2. FROM a in c.invoiceDetails

生成：

  1. [
  2.     {
  3.         "itemName": "Item 1",
  4.         "qty": 1,
  5.         "unitPrice": 100,
  6.         "lineTotal": 100
  7.     },
  8.     {
  9.         "itemName": "Item 2",
  10.         "qty": 3,
  11.         "unitPrice": 200,
  12.         "lineTotal": 600
  13.     },
  14.     {
  15.         "itemName": "Item 6",
  16.         "qty": 1,
  17.         "unitPrice": 300,
  18.         "lineTotal": 300
  19.     }
  20. ]

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

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

并得到：

  1. [
  2.     {
  3.         "SumLineTotal": 1000
  4.     }
  5. ]
英文:

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:

  1. SELECT *
  2. FROM a in c.invoiceDetails

Produces:

  1. [
  2.     {
  3.         "itemName": "Item 1",
  4.         "qty": 1,
  5.         "unitPrice": 100,
  6.         "lineTotal": 100
  7.     },
  8.     {
  9.         "itemName": "Item 2",
  10.         "qty": 3,
  11.         "unitPrice": 200,
  12.         "lineTotal": 600
  13.     },
  14.     {
  15.         "itemName": "Item 6",
  16.         "qty": 1,
  17.         "unitPrice": 300,
  18.         "lineTotal": 300
  19.     }
  20. ]

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

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

And get:

  1. [
  2.     {
  3.         "SumLineTotal": 1000
  4.     }
  5. ]

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

发表评论

匿名网友

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

确定