Different ObjectId sort order in search and sort stages

Hello,

we have a users collection with documents uniquely identified by an ObjectId in the _id field. The collection has the default _id index and a search index, that explicitly includes the _id field. When working on a bug regarding fetching pages of users, I discovered that adding an additional sort stage after the search stage leads to another order (by _id) than sorting in the search stage.

Following are (example) queries for the two cases, with example results to show the different orders:

Sorting in search stage:

[
  {
    $search: {
      compound: {
        must: [
          {
            equals: {
              path: "isDeleted",
              value: false,
            },
          },
        ],
      },
      sort: {
        _id: 1,
      },
    },
  },
  {
    $project:
      {
        _id: 1,
      },
]

Result (only first 15 entries, as I cannot upload files yet):

[{
  "_id": {
    "$oid": "63f4f50f32ab5388bc032aee"
  }
},
{
  "_id": {
    "$oid": "640d98293e8f59ea76c887b1"
  }
},
{
  "_id": {
    "$oid": "6454f19b477285aa4b09068c"
  }
},
{
  "_id": {
    "$oid": "645609a3ddd2a1e1abaf5c61"
  }
},
{
  "_id": {
    "$oid": "6456c3abddd2a1e1abb0fe8b"
  }
},
{
  "_id": {
    "$oid": "64767720042db1c2b5f88092"
  }
},
{
  "_id": {
    "$oid": "6478c849bdbf3bf53efb7051"
  }
},
{
  "_id": {
    "$oid": "6480eec77b0574f0fff67eb9"
  }
},
{
  "_id": {
    "$oid": "649142970d949a17493944cb"
  }
},
{
  "_id": {
    "$oid": "64996b905bf4f500c07d97e9"
  }
},
{
  "_id": {
    "$oid": "64b3f53674fc66ad677b8351"
  }
},
{
  "_id": {
    "$oid": "64d5dd1d52fdbbcc3fee6670"
  }
},
{
  "_id": {
    "$oid": "64ddce2752fdbbcc3f276f47"
  }
},
{
  "_id": {
    "$oid": "64f5b8723464f836ebe58f14"
  }
},
{
  "_id": {
    "$oid": "6508510711c045a9f3232f91"
  }
}]

Sorting in sort stage:

[
  {
    $search: {
      compound: {
        must: [
          {
            equals: {
              path: "isDeleted",
              value: false,
            },
          },
        ],
      },
    },
  },
  {
    $sort:
      {
        _id: 1,
      },
  },
  {
    $project:
      {
        _id: 1,
      },
  },
]

Result (only first 15 entries, as I cannot upload files yet):

[{
  "_id": {
    "$oid": "63f4f50f32ab5388bc032aee"
  }
},
{
  "_id": {
    "$oid": "640d98293e8f59ea76c887b1"
  }
},
{
  "_id": {
    "$oid": "6454f19b477285aa4b09068c"
  }
},
{
  "_id": {
    "$oid": "645609a3ddd2a1e1abaf5c61"
  }
},
{
  "_id": {
    "$oid": "6456c3abddd2a1e1abb0fe8b"
  }
},
{
  "_id": {
    "$oid": "64767720042db1c2b5f88092"
  }
},
{
  "_id": {
    "$oid": "6478c849bdbf3bf53efb7051"
  }
},
{
  "_id": {
    "$oid": "6480eec77b0574f0fff67eb9"
  }
},
{
  "_id": {
    "$oid": "649142970d949a17493944cb"
  }
},
{
  "_id": {
    "$oid": "6497e143c5385539d11f9796"
  }
},
{
  "_id": {
    "$oid": "64996b905bf4f500c07d97e9"
  }
},
{
  "_id": {
    "$oid": "64b3f53674fc66ad677b8351"
  }
},
{
  "_id": {
    "$oid": "64d5dd1d52fdbbcc3fee6670"
  }
},
{
  "_id": {
    "$oid": "64ddce2752fdbbcc3f276f47"
  }
},
{
  "_id": {
    "$oid": "64f5b8723464f836ebe58f14"
  }
}]

This issue is additionally problematic when implementing keyset pagination, based on an ObjectId field. As it is currently not possible to use ObjectId fields with the range operator in a search stage, we need to add an extra match stage to accomplish this. However the $gt operator seems to use the same ObjectId order as the additional sort stage, so we are also forced to pull the sort outside of the search stage, which would be more performant.

I am wondering if this is a bug somewhere in Atlas Search or if there is an issue with out approach. Any help is highly appreciated!

Cheers,
Fabian

1 Like

Hi @fabian , $search.sort does not currently support ObjectId types. We have a project in progress to add support for this, along with supporting ObjectIds with the $search.range operator. You can subscribe to this item in our feedback portal to get updates.