MongoDB sort / weight $or aggregated query

Question

I am doing a query with $or aggregation on two fields in the database. The first condition I want to be more relevant than the second, so I want to sort those that matches by first condition first and then return the latter after.

Here is some code:

  const regexp = new RegExp('^' + req.query.search, 'i')

  const query = {
    $or: [{ word: regexp }, { 'lang.english': regexp }],
  }

  const words = await collection
    .find(query, {
      collation: {
        locale: 'sv',
        strength: 1,
      },
      projection: {
        '-_id': 1,
        'word': 1,
        'lang.english': 1,
        'lang.definitionEnglish': 1,
        'lang.definition': 1,
      },
    })
    .skip(skips)
    .limit(page_size)
    .toArray()

So basicly, those results that matches on the word regexp should come first, then lang.english

Answer 1

To start with let's throw some data in to a MongoDB test collection to play around with.

db.test.insertMany([
    { word: 'TEST123', lang: { english: 'wibble1' } },
    { word: 'wibble2', lang: { english: 'Test123' } },
    { word: 'test345', lang: { english: 'wibble3' } },
    { word: 'wibble4', lang: { english: 'testy101' } }
]);

{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("5f6924b271e546940acc10f7"),
                ObjectId("5f6924b271e546940acc10f8"),
                ObjectId("5f6924b271e546940acc10f9"),
                ObjectId("5f6924b271e546940acc10fa")
        ]
}

This has the same structure as mentioned in the question.

Next we can build up an aggregation query that will match the same as your find but this time append an extra field that we can sort on. We'll make this 1 if word field matches then 2 for anything else.

db.test.aggregate([
   { $match:  { $or: [{ word: regexp }, { 'lang.english': regexp }] } },
   { $addFields: { sortingValue: { $cond: { if: { $regexMatch: { input: "$word" , regex: regexp } }, then: 1, else: 2 } } } }
]);

{
        "_id" : ObjectId("5f6924b271e546940acc10f7"),
        "word" : "TEST123",
        "lang" : {
                "english" : "wibble1"
        },
        "sortingValue" : 1
}
{
        "_id" : ObjectId("5f6924b271e546940acc10f8"),
        "word" : "wibble2",
        "lang" : {
                "english" : "Test123"
        },
        "sortingValue" : 2
}
{
        "_id" : ObjectId("5f6924b271e546940acc10f9"),
        "word" : "test345",
        "lang" : {
                "english" : "wibble3"
        },
        "sortingValue" : 1
}
{
        "_id" : ObjectId("5f6924b271e546940acc10fa"),
        "word" : "wibble4",
        "lang" : {
                "english" : "testy101"
        },
        "sortingValue" : 2
}

Now we can add the normal sort, skip and limting.

var regexp = new RegExp('^' + 'test', 'i')
var skip = 0;
var limit = 3;

db.test.aggregate([
   { $match:  { $or: [{ word: regexp }, { 'lang.english': regexp }] } },
   { $addFields: { sortingValue: { $cond: { if: { $regexMatch: { input: "$word" , regex: regexp } }, then: 1, else: 2 } } } },
   { $sort: { sortingValue: 1 } },
   { $skip: skip },
   { $limit: limit }
]);

{
        "_id" : ObjectId("5f6924b271e546940acc10f9"),
        "word" : "test345",
        "lang" : {
                "english" : "wibble3"
        },
        "sortingValue" : 1
}
{
        "_id" : ObjectId("5f6924b271e546940acc10f7"),
        "word" : "TEST123",
        "lang" : {
                "english" : "wibble1"
        },
        "sortingValue" : 1
}
{
        "_id" : ObjectId("5f6924b271e546940acc10f8"),
        "word" : "wibble2",
        "lang" : {
                "english" : "Test123"
        },
        "sortingValue" : 2
}