CODEWITHSUNDEEP
mongodbsortingindexing
tna
tna

Reputation: 51

Mongodb querying - How to specify index traversal order?

Context

I have a users collection with an array of key-value pairs like so:

{
  name: 'David',
  customFields: [
    { key: 'position', value: 'manager' },
    { key: 'department', value: 'HR' }
  ]
},

{
  name: 'Allison',
  customFields: [
    { key: 'position', value: 'employee' },
    { key: 'department', value: 'IT' }
  ]
}

The field names in customFields are configurable by the application users, so in order for it to be indexable I store them as an array of key-value pairs. Index { 'customFields.key': 1, 'customFields.value': 1} works quite well. Problem is when I want to retrieve the results in either order (ascending or descending). Say I want to sort all users having the custom field position in either order. The below query gives me ascending order:

db.users.find({ customFields: { $elemMatch: { key: 'position' } } })

However, I couldn't figure out how to get the opposite order. I'm pretty sure with the way the indexes are structured, if I could tell mongodb to traverse the index backwards I would get what I want. The other options is to have an other 2 indexes to cover both directions, however it's more costly.

Is there a way to specify the traversal direction? If not, what are some good alternatives? Much appreciated.

EDIT

I'd like to further clarify my situation. I have tried:

db.users.find({ customFields: { $elemMatch: { key: 'position' } } })
  .sort({ 'customFields.key': 1, 'customFields.value': 1 })

db.users.find({ customFields: { $elemMatch: { key: 'position' } } })
  .sort({'customFields.value': 1 })

These two queries only sort the documents after they have been filtered, meaning the sorting is applied on all the custom fields they have, not on the field matching the query (position in this case). So it seems using the sort method won't be of any help.

Not using any sorting conveniently returns the documents in the correct order in the ascending case. As can be seen in the explain result:

"direction" : "forward",
"indexBounds" : {
  "customFields.key" : [
    "[\"position\", \"position\"]"
  ],
  "customFields.value" : [
    "[MinKey, MaxKey]"
   ]
 }

I'm using exact match for customFields.key so I don't care about it's order. Within each value of customFields.key, the values of customFields.value are arranged in the order specified in the index, so I just take them out as it is and all is good. Now if I could do something like:

db.users.find({ customFields: { $elemMatch: { key: 'position' } } })
  .direction('backwards')

It would be the equivalent of using the index { 'customFields.key': -1, 'customFields.value': -1 }. Like I said I don't care about customFields.key order, and 'customFields.value': -1 gives me exactly what I want. I've searched mongodb documentation but I couldn't find anything similar to that.

I could retrieve all the documents matching customFields.key and do the sorting myself, but it's expensive as the number of documents could get very big. Ideally all the filtering and sorting is solved by the index.

Upvotes: 1

Views: 44

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Here is the compound index you created:

db.users.createIndex( { "customFields.key": 1, "customFields.value": 1 } )

This index will allow traversal either using both fields in ascending order:

db.users.sort( { "customFields.key": 1, "customFields.value": 1 } )

Or, it can also support traversal using both keys in descending order:

db.users.sort( { "customFields.key": -1, "customFields.value": -1 } )

If you need mixed behavior, i.e. ascending on one field but descending on the other, then you would need to add a second index:

db.users.createIndex( { "customFields.key": 1, "customFields.value": -1 } )

Note that after adding this second index, all four possible combinations are now supported.

Upvotes: 1

Related Questions