Using compound index to sort $regex filter in MongoDB

Question

I'm trying to achieve reliable sorting and index-supported pagination, while filtering documents by string field, which combines values from multiple source fields.

Given this document

{
    _id: ObjectId(...),
    fields: [
        {"type": "first_name", "value": "FirstName"},
        {"type": "last_name", "value": "LastName"},
        {"type": "phone", "value": "123456789"}
    ],
    "search": "123456789 firstname lastname"
}

i build search field by joining lowercase value from each document in fields array. By using combined field i can filter documents with $regex using only part of the value - for example, this document can match user's input firstname 6789 with following filter:

{
    $and: [
        {search: /firstname/},
        {search: /6789/},
        {fields: {$elemMatch: {value: /firstname/i}}},
        {fields: {$elemMatch: {value: /6789/i}}}
    ]
}

And, as expected, it utilizes {search: 1} index to scan 'search' field, instead of fetching it from collection immediately (https://docs.mongodb.com/manual/reference/operator/query/regex/#index-use):

{'stage': 'FETCH',
 'filter': {'$and': [{'fields': {'$elemMatch': {'value': {'$regex': 'firstname',
       '$options': 'iu'}}}},
   {'fields': {'$elemMatch': {'value': {'$regex': '6789',
       '$options': 'iu'}}}}]},
 'inputStage': {'stage': 'IXSCAN',
  'filter': {'$and': [{'search': {'$regex': 'firstname', '$options': 'u'}},
    {'search': {'$regex': '6789', '$options': 'u'}}]},
  'keyPattern': {'search': 1},
  'indexName': 'search_1',
  'isMultiKey': False,
  'multiKeyPaths': {'search': []},
  'isUnique': False,
  'isSparse': False,
  'isPartial': False,
  'indexVersion': 2,
  'direction': 'forward',
  'indexBounds': {'search': ['["", {})']}}}

Unfortunately, if i try and add sorting by _id, MongoDB doesn't seem to be able to use filter on {_id: 1, search: 1} index:

{'stage': 'FETCH',
 'filter': {'$and': [{'fields': {'$elemMatch': {'value': {'$regex': 'firstname',
       '$options': 'iu'}}}},
   {'fields': {'$elemMatch': {'value': {'$regex': '6789', '$options': 'iu'}}}},
   {'search': {'$regex': 'firstname', '$options': 'u'}},
   {'search': {'$regex': '6789', '$options': 'u'}}]},
 'inputStage': {'stage': 'IXSCAN',
  'keyPattern': {'_id': 1, 'search': 1},
  'indexName': '_id_1_search_1',
  'isMultiKey': False,
  'multiKeyPaths': {'_id': [], 'search': []},
  'isUnique': False,
  'isSparse': False,
  'isPartial': False,
  'indexVersion': 2,
  'direction': 'forward',
  'indexBounds': {'_id': ['[MinKey, MaxKey]'],
   'search': ['[MinKey, MaxKey]']}}}

I can't find exact limitations of $regex regarding compound indexes. Is it possible to achieve this behaviour?

D. SM D. SM · Accepted Answer · 2020-04-09T01:05:51

MongoDB supports full text search natively. According to my reading of this and this, it is possible to define a compound index that starts with a text index and continues with _id in any order (ascending or descending) which should satisfy your query requirements.

Unfortunately, if i try and add sorting by _id, MongoDB doesn't seem to be able to use filter on {_id: 1, search: 1} index:

The index must be defined in the order it will be used. Since filtering happens before sorting, try {search: 1, _id: 1}.

Using compound index to sort $regex filter in MongoDB

1 Answers