如何按日期范围和员工姓名筛选Azure Cosmos DB子数组

我的Azure Cosmos DB中存储有如下数据，需要基于data.date的范围以及employee.full_name包含指定内容的条件进行筛选。当前使用的查询语句返回了不符合预期的结果，仍显示全部数据，请求帮助解决。

存储的Cosmos DB数据 #

{
  "id": "ED-BSC1",
  "data": [
    {
      "date": "2023-05-09",
      "employees": [
        {
          "employee_id": "E3948",
          "full_name": "Donni",
          "loc": "Building F",
          "floor": 5
        },
        {
          "employee_id": "E9372",
          "full_name": "Viona",
          "loc": "Building F",
          "floor": 5
        },
        {
          "employee_id": "E4551",
          "full_name": "Abby",
          "loc": "Building A",
          "floor": 6
        }
      ]
    },
    {
      "date": "2023-05-10",
      "employees": [
        {
          "employee_id": "E9032",
          "full_name": "Brian",
          "loc": "Building A",
          "floor": 12
        },
        {
          "employee_id": "E9033",
          "full_name": "Emma",
          "loc": "Building A",
          "floor": 11
        },
        {
          "employee_id": "E9034",
          "full_name": "Emily",
          "loc": "Building A",
          "floor": 11
        }
      ]
    },
    {
      "date": "2023-05-11",
      "employees": [
        {
          "employee_id": "E1042",
          "full_name": "Diana",
          "loc": "Building C",
          "floor": 14
        },
        {
          "employee_id": "E5323",
          "full_name": "Dereck",
          "loc": "Building A",
          "floor": 11
        },
        {
          "employee_id": "E9034",
          "full_name": "Emily",
          "loc": "Building B",
          "floor": 19
        }
      ]
    }
  ]
}

当前使用的查询语句（未达到预期） #

SELECT
    c.id,
    c.data
from 
    c 
    join d in c.data 
    join e in d.employees   
where 
    c.id = 'ED-BSC1' 
    and d.date >= '2023-05-10' 
    and d.date <= '2023-05-11' 
    and e.full_name like '%Em%'

预期查询结果 #

{
  "id": "ED-BSC1",
  "data": [
    {
      "date": "2023-05-10",
      "employees": [
        {
          "employee_id": "E9033",
          "full_name": "Emma",
          "loc": "Building A",
          "floor": 11
        },
        {
          "employee_id": "E9034",
          "full_name": "Emily",
          "loc": "Building A",
          "floor": 11
        }
      ]
    },
    {
      "date": "2023-05-11",
      "employees": [
        {
          "employee_id": "E9034",
          "full_name": "Emily",
          "loc": "Building B",
          "floor": 19
        }
      ]
    }
  ]
}

问题原因及解决方案 #

原查询的问题在于：使用join展开数组后，直接返回c.data会获取文档的原始完整数组，并未对嵌套的data和employees进行过滤。需要通过ARRAY()函数结合子查询，逐层筛选符合条件的嵌套元素。

正确的查询语句 #

SELECT
    c.id,
    ARRAY(
        SELECT 
            d.date,
            ARRAY(SELECT * FROM e IN d.employees WHERE e.full_name LIKE '%Em%') AS employees
        FROM d IN c.data
        WHERE d.date >= '2023-05-10' AND d.date <= '2023-05-11'
        HAVING ARRAY_LENGTH(ARRAY(SELECT * FROM e IN d.employees WHERE e.full_name LIKE '%Em%')) > 0
    ) AS data
FROM c
WHERE c.id = 'ED-BSC1'

语句说明 #

1. 外层查询定位目标文档c.id = 'ED-BSC1'
2. 内层第一个子查询筛选data数组中符合日期范围的项
3. 对每个符合日期的data项，再通过子查询筛选employees数组中full_name包含Em的元素
4. 使用HAVING子句过滤掉没有符合条件员工的data项，确保最终结果只保留有匹配员工的日期条目

执行该查询后，将返回与预期一致的结果。

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