如何通过Azure CosmosDB原生查询筛选指定条件的WebId？

存储的JSON数据 #

[
    {
            "WebId": "99999996_2_10_125064",
            "SectionId": 1551,
            "rowtype": 4,
            "Fields": [
                    {
                            "FieldId": 8998,
                            "FieldValue": "1002100481",
                            "FieldName": "<p>Project_No</p>8998"
                    },
                    {
                            "FieldId": 9839,
                            "FieldValue": "Customer Name 123",
                            "FieldName": "<p>Customer_Name</p>9839"
                    }
            ]
    },
    {
            "WebId": "99999996_2_10_125064",
            "SectionId": 1552,
            "rowtype": 4,
            "Fields": [
                    {
                            "FieldId": 13362,
                            "FieldValue": "76215_1_9_122345",
                            "FieldName": "<p>LW_WebID</p>13362"
                    },
                    {
                            "FieldId": 10369,
                            "FieldValue": null,
                            "FieldName": "<p>TRM_ID</p>10369"
                    }
            ]
    }
],
[
    {
            "WebId": "99999996_2_10_125070",
            "SectionId": 1551,
            "rowtype": 4,
            "Fields": [
                    {
                            "FieldId": 8998,
                            "FieldValue": "1002100482",
                            "FieldName": "<p>Project_No</p>8998"
                    },
                    {
                            "FieldId": 9839,
                            "FieldValue": "Customer Name 124",
                            "FieldName": "<p>Customer_Name</p>9839"
                    }
            ]
    },
    {
            "WebId": "99999996_2_10_125070",
            "SectionId": 1552,
            "rowtype": 4,
            "Fields": [
                    {
                            "FieldId": 13362,
                            "FieldValue": "94215_1_9_122346",
                            "FieldName": "<p>LW_WebID</p>13362"
                    },
                    {
                            "FieldId": 10369,
                            "FieldValue": null,
                            "FieldName": "<p>TRM_ID</p>10369"
                    }
            ]
    }
]

查询需求 #

需要获取所有满足以下条件的WebId：当Fields.FieldName为<p>LW_WebID</p>13362时，对应的Fields.FieldValue匹配'76215_1_9_%'，示例数据中应仅返回WebId为'99999996_2_10_125064'的记录。

当前实现方式 #

目前只能通过以下查询拉取包含FieldId为8998、9839、13362的记录，之后在Power Query中透视数据并筛选LW_WebId：

SELECT
    c["WebId"]
    , c["SectionId"]
    , c["rowtype"]
    , d["FieldId"]
    , d["FieldName"]
    , d["FieldValue"]
FROM c
JOIN d IN c["Fields"]
WHERE
    c["WebId"] LIKE '99999996_2_10_%'
    AND d["FieldId"] IN (13362, 8998, 9839)

原生查询实现 #

可以直接通过Cosmos DB原生SQL查询完成筛选，无需后续在Power Query中处理，提供两种方案：

方案1：仅返回符合条件的WebId #

如果只需要目标WebId，可通过DISTINCT避免重复返回：

SELECT DISTINCT c["WebId"]
FROM c
JOIN d IN c["Fields"]
WHERE
    c["WebId"] LIKE '99999996_2_10_%'
    AND d["FieldName"] = '<p>LW_WebID</p>13362'
    AND d["FieldValue"] LIKE '76215_1_9_%'

方案2：返回符合条件WebId的指定Field数据 #

如果需要同时获取这些WebId对应的FieldId为8998、9839、13362的数据，可通过子查询先筛选目标WebId，再拉取对应数据：

SELECT
    c["WebId"]
    , c["SectionId"]
    , c["rowtype"]
    , d["FieldId"]
    , d["FieldName"]
    , d["FieldValue"]
FROM c
JOIN d IN c["Fields"]
WHERE
    c["WebId"] IN (
        SELECT DISTINCT c_inner["WebId"]
        FROM c_inner
        JOIN d_inner IN c_inner["Fields"]
        WHERE
            c_inner["WebId"] LIKE '99999996_2_10_%'
            AND d_inner["FieldName"] = '<p>LW_WebID</p>13362'
            AND d_inner["FieldValue"] LIKE '76215_1_9_%'
    )
    AND d["FieldId"] IN (13362, 8998, 9839)

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