如何在Cosmos DB中使用JOIN获取部门成员及员工邮箱信息

你的SQL语句返回所有员工的原因是关联员工数组时没有添加ID匹配条件，导致部门成员与所有员工产生笛卡尔积，从而得到多余结果。同时原语句存在两处笔误：em.isDepartmentHead应为dm.isDepartmentHead，c.Employees需改为小写的c.employees（Cosmos DB属性名区分大小写）。

正确SQL语句（JOIN写法） #

SELECT dm.id, dm.name, e.email, dm.isDepartmentHead
FROM Companies c
JOIN d IN c.departments
JOIN dm IN d.members
JOIN e IN c.employees
WHERE c.id = "company-a" 
  AND d.id = "hr"
  AND dm.id = e.id -- 核心条件：通过员工ID关联部门成员与员工信息

另一种写法（子查询匹配邮箱） #

如果不想使用JOIN，也可以通过子查询直接提取对应员工的邮箱：

SELECT 
  dm.id, 
  dm.name, 
  (SELECT VALUE e.email FROM e IN c.employees WHERE e.id = dm.id) AS email,
  dm.isDepartmentHead
FROM Companies c
JOIN d IN c.departments
JOIN dm IN d.members
WHERE c.id = "company-a" AND d.id = "hr"

执行结果 #

两种写法都会返回你期望的结果：

[
   {
      "id": "jane-doe",
      "name": "Jane Doe",
      "email": "jane.doe@example.com",
      "isDepartmentHead": true
   }
]

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