求助：在Cosmos中展平多维数组并筛选关联数据

需求梳理 #

• 筛选条件1：Project的startDate在过去一年内
• 筛选条件2：Resource关联的sourceSystem为指定值（如"Z"）
• 输出字段：personName（Resource的人员名称）、sourceSystem（匹配的源系统）、name（所属Project名称）

查询语句示例 #

以下是适配你数据结构的Cosmos DB SQL查询，替换@TargetSourceSystem参数值即可切换目标源系统：

SELECT 
    r.personName,
    pk.sourceSystem,
    p.name
FROM 
    p
JOIN 
    r IN p.resources
JOIN 
    pk IN r.primaryKeys
WHERE 
    p.startDate >= DATEADD(year, -1, GETDATE())
    AND pk.sourceSystem = @TargetSourceSystem

语句说明 #

1. 多层JOIN展平：通过两次JOIN分别拆分Project下的resources数组、每个Resource下的primaryKeys数组，实现多维数组的扁平化处理
2. 时间阈值筛选：用DATEADD(year, -1, GETDATE())计算过去一年的时间节点，过滤出符合时间要求的Project
3. 源系统精准匹配：直接匹配primaryKeys中的sourceSystem字段，仅保留目标系统的关联记录
4. 指定字段输出：提取需求中的三个字段，输出符合格式要求的结果

测试数据适配提示 #

你提供的测试Project示例startDate为2016年，不在过去一年内，因此不会被查询命中。若将startDate修改为近一年日期（如2023-10-01T00:00:00），查询将返回两条结果：

• Jane Doe, Z, Test Project
• John Smith, Z, Test Project

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