Sorting with $elemMatch on array indexes

Working with Data
, ,
1

Hi there,

Say I have an array of items in a collection, and I index two properties in the items, I can do queries on the properties and it will use the correct compound index bounds:

db.test.insertMany([
  { items: [{ a: 2, b: 2 }] },
  { items: [{ a: 1, b: 7 }] },
  { items: [{ a: 2, b: 5 }] },
  { items: [{ a: 1, b: 2 }] },
]);

db.test.createIndex({ 'items.a': 1, 'items.b': 1 });

db.test.find({
  items: {
    $elemMatch: {
      a: { $gt: 0 },
      b: { $gt: 0 }
    }
  }
});

The documents will also be returned in ascending order of the indexes:

[
  {
    _id: ObjectId("642658957af10b1de797d5ef"),
    items: [ { a: 1, b: 2 } ]
  },
  {
    _id: ObjectId("642658957af10b1de797d5ed"),
    items: [ { a: 1, b: 7 } ]
  },
  {
    _id: ObjectId("642658957af10b1de797d5ec"),
    items: [ { a: 2, b: 2 } ]
  },
  {
    _id: ObjectId("642658957af10b1de797d5ee"),
    items: [ { a: 2, b: 5 } ]
  }
]

When I specify a sort, it will always do an in-memory sort though. Is there a way to sort the results with use of the index? Or, if not, is there a way to return the results in naturally reversed order for the index?

I have tried some of the following:

.sort({ 'items.a': 1 })
.sort({ 'items.b': 1 })
.sort({ 'items.a': 1, 'items.b': 1 })

If this is not possible, I know I can just split the items array off into a separate collection and index on it, but I just would like to know if this sort of sorting is possible before I do this.

Many thanks!

3

Hello :wave: @Joseph_Dunne,

Welcome to the MongoDB Community forums :sparkles:

In short, it’s not guaranteed that specifying the sort() method in MongoDB will always result in an in-memory sort.

To elaborate, there are certain conditions when MongoDB cannot use the sorted nature of the index and has to perform an in-memory SORT stage. This happens when the query cannot use the “index prefix,” which means that the index cannot guarantee the sorting order of the returned documents. In this situation, MongoDB has to perform an in-memory sort to return the results in the desired order.

For example:

db.test.find({ items: { $elemMatch: { a: { $gt: 0 }, b: { $gt: 0 } } } }).sort({'items.b':1})

  • In the query above, the index {items.a:1,items.b:1} can be used to match documents having items.a greater than 0 for the {a:{$gt:0}} portion of the query.

  • However, there is no guarantee that the returned documents are sorted in terms of items.b.

Therefore, MongoDB has no choice but to perform an in-memory sort. The explain() output of this query will have a SORT stage.

 winningPlan: {
    stage: 'SORT',
    sortPattern: { 'items.b': 1 }
    ...

Whereas in another scenario MongoDB can use the sorted nature of the index if the query specifies sort keys that match the order of the index and the same ordering as the index.

For example:

the index (i.e. the index {a:1,b:1} can be used for sort({a:1,b:1}) or sort({a:-1,b:-1}) but not sort({a:1,b:-1}) and sort({'items.a':1})

Here, in this case

db.test.find({ items: { $elemMatch: { a: { $gt: 0 }, b: { $gt: 0 } } } }).sort({'items.a':1})

MongoDB can guarantee that the returned documents are sorted in terms of the specified key. This means that the explain() output of the query will not have a SORT stage and the sort() is essentially free.

winningPlan: {
   stage: 'FETCH',
   filter: {
   ...
rejectedPlans: [
    {
     stage: 'SORT',
     sortPattern: { 'items.a': 1 },
     ...

You can do it by specifying the sort({'items.a':-1,'items.b':-1}) and it will not perform an in-memory SORT.

db.test.find({ items: { $elemMatch: { a: { $gt: 0 }, b: { $gt: 0 } } } }).sort({'items.a':-1,'items.b':-1})

It will return the following output (reversed order):

[
  {
    _id: ObjectId("64382e81aa700bea8961598e"),
    items: [ { a: 2, b: 5 } ]
  },
  {
    _id: ObjectId("64382e81aa700bea8961598c"),
    items: [ { a: 2, b: 2 } ]
  },
  {
    _id: ObjectId("64382e81aa700bea8961598d"),
    items: [ { a: 1, b: 7 } ]
  },
  {
    _id: ObjectId("64382e81aa700bea8961598f"),
    items: [ { a: 1, b: 2 } ]
  }
]

I hope it clarifies your doubt. Let us know if you have any further questions.

Best,
Kushagra

3 Likes
4

Hi there,

Many thanks for your response. I have tried again and it is now sorting using the index when I specify one of the following sorts:

{ 'items.a': 1 }
{ 'items.a': 1, 'items.b': 1 }

I recently updated to Mongo version 6, whereas I don’t recall this happening in version 4 and 5. Do you know if this a new feature in version 6?

Many thanks,