编写Cosmos查询以检索含重复type1类型tokenValue的记录

需求：检索所有包含重复tokenValue的记录，且这些tokenValue对应的条目类型为type1。

示例数据 #

记录1

{
    "id": "1234",
    "tokens": [
        {
            "type": "type1",
            "tokenValue": "1234"
        },
        {
            "type": "type2",
            "tokenValue": "5678"
        }
    ]
}

记录2

{
    "id": "abcd",
    "tokens": [
        {
            "type": "type1",
            "tokenValue": "1234"
        },
        {
            "type": "type2",
            "tokenValue": "91011"
        }
    ]
}

查询语句 #

WITH TokenCounts AS (
    SELECT 
        t.tokenValue,
        COUNT(1) AS count
    FROM 
        c
    JOIN 
        t IN c.tokens
    WHERE 
        t.type = 'type1'
    GROUP BY 
        t.tokenValue
    HAVING 
        COUNT(1) > 1
)
SELECT 
    c.*
FROM 
    c
JOIN 
    t IN c.tokens
JOIN 
    tc IN TokenCounts
WHERE 
    t.type = 'type1' AND t.tokenValue = tc.tokenValue

查询逻辑 #

• 先通过TokenCounts公共表达式，统计所有type1类型tokenValue的出现次数，筛选出重复（次数>1）的tokenValue。
• 再将原集合与tokens数组展开，关联筛选后的TokenCounts，得到包含重复type1类型tokenValue的完整记录。

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