Mongodb aggregate – Sort then get surrounding documents

April 19, 2023

I’m trying to write a mongodb aggregate to apply a 2-level sort and then get the 2 documents before and after a particular document in the sorted set. To start, I’m trying to build a query for the "after" document

Documents are inserted out of order:

db.test.insertMany([
   { name: 'V', type: 'Trap' }, 
   { name: 'J', type: 'Magic' },
   { name: 'B', type: 'Effect' }
])

To sort alphabetically within each type I can do:

db.test.aggregate([{ "$sort": { type: 1, name: 1 } }])

Result:
{ name: 'B', type: 'Effect' }
{ name: 'J', type: 'Magic' }
{ name: 'V', type: 'Trap' }

I’m trying to use the following query (with replacements) to get the "after" document:

[
  { $sort: { type: 1, name: 1 } },
  {
    $match: {
      name: {
        $gt: <name>,
      },
      type: {
        $gte: <type>,
      },
    },
  },
  { $limit: 1 }
]

For example:

Document { name: 'K', type: 'Trap' } should go between J & V.

The above query correctly gives "V".

Document { name: 'C', type: 'Effect' } should go between B & J.

The above query correctly gives "J".

However, document{ name: 'K', type: 'Effect' } also needs to go between B & J since it should remain in the "Effect" grouping.

The query incorrectly returns "V" again (should be J).

Removing either of the $match properties gives incorrect results in other cases. Can’t seem to use $and/or to get correct results in all cases either

For the reverse, my "before" query is as following (not working in all cases):

{ $sort: { type: -1, name: -1 } },
            {
               $match: {
                  name: { $lt: card.name },
                  type: { $lte: card.type }
               }
            },
{ $limit: 1 }

>Solution :

can write a match query which checks an or condition where check type is greater than the given type or if the type is equal to the given type then check if the name is greater than the given name. To get the before document replace gt with lt

db.collection.aggregate([
  { $sort: { type: 1, name: 1 } },
  {
    $match: {
      $or: [
        { type: { $gt: <type> } },
        { $and: [{ type: <type> }, { name: { $gt: <name> } }] }
      ]
    }
  },
  { $limit: 1 }
])

playground