CosmosDB单查询实现指定条件数据提取与统计需求

存储的JSON数据 #

{"id": "zzzzzzz","organizationId": "xxxxxxxx","agentId": "yyyyyyyyyyy","createdAt": "2023-05-03T16:46:53.77Z","createdAtUtc": "2023-05-03T07:16:53.78Z","content": {"modelAt": "2023-05-03T16:46:53.77Z","modelAtUtc": "2023-05-03T07:16:53.78Z","machineId": "99999","driverId": "777","operatorId": "88","collectedDate": "Wednesday, 3 May 2023","totalCount": 18.9,"sequenceNumber": 1000024,"totalQuantity": 0,"values": {"created": "2023-05-02T14:59:15Z","version": "1.28.1588","serialNumber": "4087","site": "Site","installationDate": "0001-01-01T00:00:00Z","setup": {"file": "xxxx.ini","name": "xxxx","encoding": "Windows-1252","decimals": 2,"model": {"sequenceNumber": 1000024,"startTime": "2023-05-02T14:59:03Z","endTime": "2023-05-02T14:59:13Z","total": 18.9,"expectedTotal": 0,"identities": [{"name": "Box","value": 123,"inHierarchy": "yes"},{"name": "Area","value": 8,"inHierarchy": "yes"}],"types": {"reject": "1","total": 0.4,"type": [{"mediaType": "type","name": "Reject","label": "R","value": 0,"count": 1,"total": 0},{"mediaType": "type","name": "1$","label": "","value": 100,"count": 0,"total": 123},{"mediaType": "type","name": "50c","label": "","value": 50,"count": 5,"total": 5234},{"mediaType": "type","name": "20c","label": "","value": 20,"count": 2,"total": 2656},{"mediaType": "type","name": "10c","label": "","value": 10,"count": 0,"total": 0},{"mediaType": "type","name": "5c","label": "","value": 5,"count": 0,"total": 234},{"mediaType": "type","name": "1$","label": "o","value": 100,"count": 0,"total": 0},{"mediaType": "type","name": "50c","label": "o","value": 50,"count": 0,"total": 0},{"mediaType": "type","name": "20c","label": "o","value": 20,"count": 0,"total": 876},{"mediaType": "type","name": "10c","label": "o","value": 10,"count": 4,"total": 0.4},{"mediaType": "type","name": "5c","label": "o","value": 5,"count": 0,"total": 867},{"mediaType": "type","name": "1c","label": "o","value": 1,"count": 0,"total": 45}]}}}}}}

需求 #

• 提取mediaType为'type'的条目中的value、count、total字段值
• 分别计算label为空字符串('')和label为'o'时的total总和，按sequenceNumber分组

现有单独查询 #

以下是两个可实现需求的单独查询：

查询label为'o'的总和 #

SELECT SUM(coin.count) AS oldSeriesTotal 
FROM c 
JOIN coin IN c.content.values.setup.transaction.coins.coin 
WHERE c.agentId = 'xxxx' AND coin.label = 'o' 
GROUP BY c.content.values.setup.transaction.sequenceNumber

查询label为空的总和 #

SELECT SUM(coin.count) AS oldSeriesTotal 
FROM c 
JOIN coin IN c.content.values.setup.transaction.coins.coin 
WHERE c.agentId = 'xxxx' AND coin.label = '' 
GROUP BY c.content.values.setup.transaction.sequenceNumber

合并后的查询方案 #

使用CosmosDB支持的CASE表达式进行条件聚合，可在一个查询中同时完成字段提取和两种label的总和计算：

SELECT 
    c.content.values.setup.model.sequenceNumber,
    SUM(CASE WHEN coin.label = '' THEN coin.total ELSE 0 END) AS emptyLabelTotal,
    SUM(CASE WHEN coin.label = 'o' THEN coin.total ELSE 0 END) AS oLabelTotal,
    ARRAY_AGG({
        "value": coin.value,
        "count": coin.count,
        "total": coin.total
    }) AS typeEntries
FROM c
JOIN coin IN c.content.values.setup.model.types.type
WHERE 
    c.agentId = 'xxxx'
    AND coin.mediaType = 'type'
    AND (coin.label = '' OR coin.label = 'o')
GROUP BY c.content.values.setup.model.sequenceNumber

关键说明 #

1. 修正了数据路径：原JSON结构中目标数组是c.content.values.setup.model.types.type，而非你原查询中的transaction.coins.coin，需匹配实际存储路径
2. 用CASE表达式实现条件求和，分别统计两种label的total总和
3. 通过ARRAY_AGG收集所有符合mediaType='type'条件的字段，满足提取需求
4. 保持按sequenceNumber分组的逻辑，与原查询一致

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