如何在Cosmos DB SQL中基于子对象属性筛选含测试URL的文档？

需求 #

获取UITemplates中包含测试URL（URL字符串包含-test）的Cosmos DB文档，返回其id列表。

示例输入文档 #

{
    "id": "id1",
    "EntityType": "metadata",
    "UITemplates": {
        "p1": [
            {
                "DisplayText": "UiDefinition.json",
                "UrlString": "https://1-test",
                "SubType": "Custom"
            },
            {
                "DisplayText": "createuidefinition",
                "UrlString": "https://2-prod",
                "SubType": "Custom"
            }
        ],
        "d3": [
            {
                "DisplayText": "UiDefinition.json",
                "UrlString": "https://11-test",
                "SubType": "Custom"
            },
            {
                "DisplayText": "createuidefinition",
                "UrlString": "https://22-test",
                "SubType": "Custom"
            }
        ]
    }
}

期望输出 #

[{"id": "id1"}]

问题卡点 #

UITemplates为动态键值对结构（属性名不固定），无法直接将其转换为数组进行JOIN或过滤。尝试的扁平化查询返回空结果；仅当预先知道UITemplates的属性名时能写出有效查询，但需要适配任意属性名的通用方案。

解决方案 #

使用Cosmos DB的OBJECT_TO_ARRAY函数将UITemplates对象转换为键值对数组，再通过多层JOIN遍历所有模板项，最后过滤出符合条件的文档ID：

SELECT DISTINCT {"id": c.id}
FROM c
JOIN prop IN OBJECT_TO_ARRAY(c.UITemplates)
JOIN template IN prop[1]
WHERE CONTAINS(template.UrlString, "-test")

说明 #

1. OBJECT_TO_ARRAY(c.UITemplates)：将UITemplates的每个属性转换为[属性名, 属性值]格式的数组元素，比如把"p1": [...]转成["p1", [...]]
2. 第一层JOIN遍历所有转换后的键值对数组元素
3. 第二层JOIN遍历每个键值对中的属性值（即模板数组prop[1]）
4. CONTAINS(template.UrlString, "-test")：筛选URL包含测试标识的模板项
5. DISTINCT：确保同一个文档即使有多个符合条件的模板，也只返回一次id

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