Cosmos DB查询：统计含Waiting-Reconciliation状态行项的唯一库存编号数

需求 #

需要统计Cosmos DB中文档中，关联lineItems状态为Waiting-Reconciliation的唯一stockNumber数量（命名为stockCount），以及对应的符合条件的lineItems总数（命名为lineItemCount）。

数据示例 #

Doc-1 #

{
    "stockNumber": "123",
    "lineItems": [
        {
            "itemState": "Reconciled"
        },
        {
           "itemState": "Waiting-Reconciliation"
        },
        {
            "itemState": "Waiting-Reconciliation"
        }
    ]
}

Doc-2 #

{
    "stockNumber": "234",
    "lineItems": [
        {
            "itemState": "Waiting-Reconciliation"
        }
    ]
}

Doc-3 #

{
    "stockNumber": "345",
    "lineItems": [
        {
            "itemState": "Reconciled"
        },
        {
            "itemState": "Reconciled"
        }
    ]
}

期望输出 #

"123"和"234"包含Waiting-Reconciliation状态的行项，符合统计条件，期望输出如下：

[
    {
        "stockCount": 2,
        "lineItemCount": 3
    }
]

查询尝试及问题 #

1. 带DISTINCT的查询执行报错：

SELECT COUNT(DISTINCT c.stockNumber) AS stockCount, 
COUNT(c.lineItems) AS lineItemCount 
FROM c JOIN l IN c.lineItems 
WHERE l.itemState = 'Waiting-Reconciliation'

错误信息：

{"code":"BadRequest","message":"One of the input values is invalid.\r\nActivityId: 1b7d21f5-2d83-4e9a-834c-26729f5bad1d, Windows/10.0.20348 cosmos-netstandard-sdk/3.18.0"}

2. 移除DISTINCT后，查询返回两个字段均为3，不符合需求：

SELECT 
count(c.stockNumber) AS stockCount, 
count(c.lineItems) AS lineItemCount 
FROM c JOIN l IN c.lineItems 
WHERE l.itemState = 'Waiting-Reconciliation'

3. 以下查询可执行，但无法直接输出统计数值：

SELECT
  DISTINCT c.stockNumber AS stockCount,
  c.lineItems AS lineItemCount
FROM c JOIN l 
IN c.lineItems 
WHERE l.itemState = 'Waiting-Reconciliation'

4. 现有两个独立查询可分别得到正确结果，但需要合并为一个查询：

• 查询1（获取stockCount）：

SELECT COUNT(c) AS stockCount 
FROM c WHERE ARRAY_LENGTH(c.lineItems) > 0 
AND EXISTS (SELECT VALUE l FROM l IN c.lineItems WHERE STRINGEQUALS(l.itemState, 'Waiting-Reconciliation', true))

结果：

[
    {
        "stockCount": 2
    }
]

• 查询2（获取lineItemCount）：

SELECT
  COUNT(l) AS lineItemCount
FROM c
  JOIN l IN c.lineItems 
WHERE l.itemState = 'Waiting-Reconciliation'

结果：

[
    {
        "lineItemCount": 3
    }
]

解决方案 #

通过子查询交叉连接的方式，将两个统计结果合并为一个查询，得到期望的输出格式：

SELECT 
    s.stockCount,
    li.lineItemCount
FROM (
    SELECT COUNT(c) AS stockCount 
    FROM c 
    WHERE ARRAY_LENGTH(c.lineItems) > 0 
    AND EXISTS (SELECT VALUE l FROM l IN c.lineItems WHERE STRINGEQUALS(l.itemState, 'Waiting-Reconciliation', true))
) s
JOIN (
    SELECT COUNT(l) AS lineItemCount
    FROM c
    JOIN l IN c.lineItems 
    WHERE l.itemState = 'Waiting-Reconciliation'
) li

执行该查询后，将直接返回包含两个统计字段的结果：

[
    {
        "stockCount": 2,
        "lineItemCount": 3
    }
]

内容的提问来源于stack exchange，提问作者GThree