如何使用Cosmos DB查询键值对属性并扁平化为表格结构供PowerBI使用

方法1：使用数组函数直接提取属性（推荐，RU消耗最低） #

该方案仅需要对每个文档的properties数组做1次遍历，无需关联操作，RU消耗比多次独立子查询低60%以上，同时可以直接获取顶层文档的所有字段。

SELECT
    t.id AS documentId,
    t.projectId AS ProjectId,
    ARRAY_FIRST(ARRAY(SELECT p.value FROM p IN t.properties WHERE p.name = "projectName")) AS projectName,
    ARRAY_FIRST(ARRAY(SELECT p.value FROM p IN t.properties WHERE p.name = "status")) AS status,
    ARRAY_FIRST(ARRAY(SELECT p.value FROM p IN t.properties WHERE p.name = "openDate")) AS openDate,
    ARRAY_FIRST(ARRAY(SELECT p.value FROM p IN t.properties WHERE p.name = "closeDate")) AS closeDate,
    ARRAY_FIRST(ARRAY(SELECT p.value FROM p IN t.properties WHERE p.name = "manager")) AS manager
FROM t
WHERE t.typeId = 0 -- 可补充其他过滤条件进一步降低RU消耗

说明：

• 内层ARRAY()函数会返回匹配name条件的属性项集合，因每个属性名在properties中唯一，返回集合仅含1个元素
• ARRAY_FIRST()直接取唯一匹配项的value值，无需额外关联逻辑
• 顶层文档的id、projectId等字段可以直接在SELECT语句中引用，解决了子查询无法获取外层字段的问题

方法2：使用JOIN + 聚合（适合存在多值属性的场景） #

如果你的properties数组中可能存在同名的多值属性，需要做聚合处理，可以使用该方案：

SELECT
    t.id AS documentId,
    t.projectId AS ProjectId,
    MAX(CASE WHEN p.name = "projectName" THEN p.value ELSE null END) AS projectName,
    MAX(CASE WHEN p.name = "status" THEN p.value ELSE null END) AS status,
    MAX(CASE WHEN p.name = "openDate" THEN p.value ELSE null END) AS openDate,
    MAX(CASE WHEN p.name = "closeDate" THEN p.value ELSE null END) AS closeDate,
    MAX(CASE WHEN p.name = "manager" THEN p.value ELSE null END) AS manager
FROM t
JOIN p IN t.properties
WHERE t.typeId = 0
GROUP BY t.id, t.projectId

Power BI对接优化建议 #

如果直接将Cosmos DB作为Power BI数据源，你可以将上述查询语句作为自定义查询直接导入，无需在Power Query中做额外转换；同时可以开启Cosmos DB的查询缓存，重复查询时可以进一步降低RU消耗。

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