如何编写Cosmos DB查询筛选指定日期范围的SubmittedDate文档

容器中的原始文档 #

[
    {
        "episodeID": "A500057110",
        "serviceRequest": {
            "listOfServiceRequest": {
                "serviceRequestKey": "A500057110",
                "dates": [
                    {
                        "type": "SubmittedDate",
                        "value": "03/26/2025 15:50:11"
                    },
                    {
                        "type": "DueDate",
                        "value": "05/15/2026 15:50:11"
                    }
                ],
                "requestStatus": "Cancelled"
            },
            "clientSystem": "Two"
        },
        "id": "fcfd621e-9c7b-0b40-0a86-4962d7bbe54d"
    },
    {
        "episodeID": "B340057110",
        "serviceRequest": {
            "listOfServiceRequest": {
                "serviceRequestKey": "C214354353",
                "dates": [
                    {
                        "type": "SubmittedDate",
                        "value": "01/16/2024 15:50:11"
                    },
                    {
                        "type": "DueDate",
                        "value": "07/01/2028 15:50:11"
                    }
                ],
                "requestStatus": "Approved"
            },
            "clientSystem": "One"
        },
        "id": "fcfd621e-9c7b-0b40-0a86-4962d7bbe54d"
    }
]

用户的错误查询（返回空结果） #

SELECT * FROM c WHERE c.serviceRequest.listOfServiceRequest.dates <= '{{type: SubmittedDate, value: 01/01/2020}}' and c.serviceRequest.listOfServiceRequest.dates >= '{{type: SubmittedDate, value: 01/01/2024}}'

错误原因 #

1. 直接对整个dates数组做对象比较，不符合Cosmos DB的数组查询逻辑，需遍历数组元素定位目标对象
2. 日期字符串未转换为datetime类型，无法实现正确的范围比较
3. 范围条件逻辑颠倒，导致查询范围错误

正确的查询语句 #

SELECT *
FROM c
JOIN d IN c.serviceRequest.listOfServiceRequest.dates
WHERE d.type = "SubmittedDate"
AND DateTimeFromParts(
    SUBSTRING(d.value, 6, 4),
    SUBSTRING(d.value, 0, 2),
    SUBSTRING(d.value, 3, 2),
    SUBSTRING(d.value, 11, 2),
    SUBSTRING(d.value, 14, 2),
    SUBSTRING(d.value, 17, 2)
) BETWEEN DATETIME('2020-01-01T00:00:00') AND DATETIME('2024-05-01T23:59:59')

关键说明 #

• 使用JOIN遍历dates数组中的每个元素d，精准定位目标日期对象
• 先筛选type为SubmittedDate的元素，缩小查询范围
• 通过DateTimeFromParts将MM/dd/yyyy HH:mm:ss格式的字符串转换为datetime类型，确保日期比较的准确性
• 用BETWEEN明确指定目标日期范围（2020-01-01至2024-05-01）

期望的查询结果 #

{
    "episodeID": "B340057110",
    "serviceRequest": {
        "listOfServiceRequest": {
            "serviceRequestKey": "C214354353",
            "dates": [
                {
                    "type": "SubmittedDate",
                    "value": "01/16/2024 15:50:11"
                },
                {
                    "type": "DueDate",
                    "value": "07/01/2028 15:50:11"
                }
            ],
            "requestStatus": "Approved"
        },
        "clientSystem": "One"
    },
    "id": "fcfd621e-9c7b-0b40-0a86-4962d7bbe54d"
}

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