Sort two datatime field produces wrong order

[chenzhe015 (哲 陈)]

I am trying to sort two fields of type DateTime.However, I found that the output is not sorted according to the fields I specified.

this is my aggregation query ;

db.t65794cee3678c8894fef567c.aggregate([{
    "$match": {
        "IsDeleted": false,
        
    }
}, {
    "$sort": {
        "CreatedTime": - 1,
        "shipPlanDate": - 1
    }
}, {
    "$limit": 20
}, {
    "$project": {
        
        "CreatedTime": 1,
        "shipPlanDate": 1,
        
    }
}]);

the follow is output

// 1
{
    "_id": "6657eec105736b287df30ae6",
    "CreatedTime": ISODate("2024-05-30T03:13:05.965Z"),
    "shipPlanDate": ISODate("2024-05-30T02:14:47.000Z")
}

// 2
{
    "_id": "6657eeb605736b287df307b7",
    "CreatedTime": ISODate("2024-05-30T03:12:54.819Z"),
    "shipPlanDate": ISODate("2024-05-30T02:14:47.000Z")
}

// 3
{
    "_id": "6657eba605736b287df29f99",
    "CreatedTime": ISODate("2024-05-30T02:59:50.753Z"),
    "shipPlanDate": ISODate("2024-05-30T02:14:47.000Z")
}

// 4
{
    "_id": "6657e6a6007b9ec1404c7a14",
    "CreatedTime": ISODate("2024-05-30T02:38:30.916Z"),
    "shipPlanDate": ISODate("2024-05-31T02:16:48.000Z")
}

// 5
{
    "_id": "6657e69e007b9ec1404c79f6",
    "CreatedTime": ISODate("2024-05-30T02:38:22.91Z"),
    "shipPlanDate": ISODate("2024-05-30T02:14:47.000Z")
}

// 6
{
    "_id": "6657e19c05736b287df1217d",
    "CreatedTime": ISODate("2024-05-30T02:17:00.652Z"),
    "shipPlanDate": ISODate("2024-05-31T02:16:48.000Z")
}

// 7
{
    "_id": "6657e17a007b9ec1404baebf",
    "CreatedTime": ISODate("2024-05-30T02:16:26.647Z"),
    "shipPlanDate": ISODate("2024-05-31T02:16:03.000Z")
}

// 8
{
    "_id": "6657e14505736b287df1112e",
    "CreatedTime": ISODate("2024-05-30T02:15:33.137Z"),
    "shipPlanDate": ISODate("2024-05-30T02:14:47.000Z")
}

// 9
{
    "_id": "6656cd51906c684d919ac661",
    "CreatedTime": ISODate("2024-05-29T06:38:09.391Z"),
    "shipPlanDate": ISODate("2024-06-03T06:36:35.000Z")
}

// 10
{
    "_id": "6656c79f906c684d919ab27e",
    "CreatedTime": ISODate("2024-05-29T06:13:51.229Z")
}

// 11
{
    "_id": "66568431de2d5290be952544",
    "CreatedTime": ISODate("2024-05-29T01:26:09.558Z"),
    "shipPlanDate": ISODate("2024-06-01T01:24:11.000Z")
}

The 4th data row should not appear in this position.

[Akshat_Gupta3 (Akshat Gupta)]

Hi Chenzhe,

The output is correct, when you pass multiple fields in the $sort stage, the first one acts as the primary sort field and the subsequent fields as a secondary sort field which only act in case there is a tie when sorting with the previous field.

For example -
If my original data looks like this -

{ "_id": 1, "class": 5, "score": 85 }
{ "_id": 2, "class": 5, "score": 92 }
{ "_id": 3, "class": 4, "score": 88 }
{ "_id": 4, "class": 5, "score": 85 }
{ "_id": 5, "class": 4, "score": 91 }

and in sort i pass -

{
        $sort: {
            class: 1,  // Ascending order
            score: -1  // Descending order
        }
    }

then in the output i will get -

{ "_id": 5, "class": 4, "score": 91 }
{ "_id": 3, "class": 4, "score": 88 }
{ "_id": 2, "class": 5, "score": 92 }
{ "_id": 1, "class": 5, "score": 85 }
{ "_id": 4, "class": 5, "score": 85 }

Notice how the score acts as a tiebreaker when the sort initially happens with class field.
You can read about sort from here

[chenzhe015 (哲 陈)]

@Akshat_Gupta3 thank you for your help，The problem is solved，its my misstake