Cosmos DB中OperatorsLog容器分页查询calls并按effectiveAt排序问题

问题背景 #

有一个名为OperatorsLog的Cosmos容器，文档结构如下：

{
    "id": "48ce03ae-6a13-4113-8b34-d5e4ad741adc",
    "date": "2023-12-01",
    "operator": {
        "id": "063911f5-c92c-470a-a0d1-141d9f820670"
    },
    "calls": [
        {
            "id": "50149c5b-353e-44ec-a22c-e0e7c9e63ea4",
            "text": "blah blah blah 1",
            "effectiveAt": "2023-12-01T14:11:23"
        },
        {
            "id": "6c65e34a-11d5-4aa3-88be-9bd93a4c878c",
            "text": "blah blah blah 2",
            "effectiveAt": "2023-12-01T13:10:22"
        }
    ]
}

需求是获取指定日期范围内文档中的calls数据，返回按effectiveAt排序的分页结果。但编写的查询语句中ORDER BY子句不生效，结果始终按原文档中记录的顺序返回。

原查询语句：

SELECT VALUE evnt
FROM OperatorsLog log
JOIN evnt IN log.calls
WHERE log.operator.id='063911f5-c92c-470a-a0d1-141d9f820670'
AND log.date>='2023-09-26'
AND log.date<='2023-12-04'
ORDER BY log.calls.effectiveAt
OFFSET 10 LIMIT 5

问题原因 #

原查询中ORDER BY使用的是log.calls.effectiveAt，这是引用整个calls数组的属性，而非通过JOIN拆分出来的单个call对象的effectiveAt字段，导致排序逻辑失效，结果只能按原文档中数组的顺序返回。

解决方法 #

将ORDER BY的字段改为evnt.effectiveAt，因为evnt是JOIN后得到的单个call实例，直接引用它的effectiveAt字段才能实现正确排序。修改后的查询语句如下：

SELECT VALUE evnt
FROM OperatorsLog log
JOIN evnt IN log.calls
WHERE log.operator.id='063911f5-c92c-470a-a0d1-141d9f820670'
AND log.date>='2023-09-26'
AND log.date<='2023-12-04'
ORDER BY evnt.effectiveAt
OFFSET 10 LIMIT 5

如果需要倒序排序，可在字段后添加DESC：

ORDER BY evnt.effectiveAt DESC

性能优化建议 #

如果数据量较大，建议创建包含log.operator.id、log.date和evnt.effectiveAt的复合索引，以提升查询和排序的性能。

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