Paging and joining arrays from different documents in Cosmos

Question

Given a Cosmos container "OperatorsLog" with the following sample document structure. I'd like to get the calls from a given date range of documents. I'd like to return paged results ordered by effectiveAt...

    {
        "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"
            }
        ]
    }

I came up with the following sample query, which seems to work, except that the ORDER BY clause does not work. I always get the records returned by the order they appear in the range of records. I fear I may be asking too much of Cosmos. Maybe there is a better approach. Thanks in advance for any help.

    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

Answer 1

The ORDER BY clause in the provided query is not working as expected because it is not ordering the results based on the order of the calls array within each document. In order to sort within the calls array, you can use the .sort() method to sort the array of calls. It compares a and b based on their effectiveAt timestamps, as you can see in the code below.

UDF I tried with:

function sortCalls(calls) {
    return calls.sort(function(a, b) {
        var dateA = new Date(a.effectiveAt);
        var dateB = new Date(b.effectiveAt);

        return dateA - dateB;
    });
}

Query:

SELECT c.id, c.date, c.operator, udf.sortCalls(c.calls) AS sortedCalls
FROM c
WHERE c.id = '48ce03ae-6a13-4113-8b34-d5e4ad741adc'

Output:

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