How can I manually handle paging in my MongoDB Aggregation?

Question

In my MongoDB I have a team collection and a group collection.

Here is an example of a team document:

'_id': ObjectId('62724a2effffbd4c82eafb1b'),
'name': 'Team A'

And here is an example of a group document:

'_id': ObjectId('6139f790f6a0af36d700c4fb'),
'name': 'Test Group',
'teams': [
    {
        'division': 1,
        'teamId': ObjectId('62724a2effffbd4c82eafb1b')
    },
    {
        'division': 1,
        'teamId': ObjectId('6139f795f6a0af373900e2f7')
    },
    ...
]

I'm trying to make an aggregation query that returns the following data:

1. A list of teams that is part of Test Group, paged with up to 100 teams.
2. A count of how many teams that was found in total.
3. A first boolean property that's true if paging is on the first page.
4. A last boolean property that's true if paging is on the last page.

The query should of course take a page number and a pagesize number. The returned value should look like this:

{
    'content': [
        {
            'id': '62724a2effffbd4c82eafb1b',
            'name': 'Team A'
        },
        {
            'id': '6139f795f6a0af373900e2f7',
            'name': 'Whatever Team'
        },
        ...
    ],
    'last': false,
    'first': true,
    'totalElements': 132
}

What I have so far:

{
    $match: {
        _id: ObjectId('6139f790f6a0af36d700c4fb')
    }
},
{
    $lookup: {
        from: "team",
        localField: "teams.teamId",
        foreignField: "_id",
        as: "teams"
    }
}

From here I've tried a few different things. I've tried to use the facet operator to build up the different properties, but then I can't fit the paging part in. I just don't know which way to go from here.

Answer 1

Your approach of using $facet is a sensible one.

• content: $sort by some order.(choose your own ordering, for the sample, _id is used). $skip by <page number> * <page size>. Then $limit by <page size>
• total: $group unconditionally with $sum: 1 to compute the total
• first: compare <page number> with 0
• last: the trick here is to skip once more. (i.e. <page number> + 1 * <page size>). If an empty result is returned, then this is the last page; otherwise, it is not.

db.group.aggregate([
  {
    "$match": {
      "_id": ObjectId("6139f790f6a0af36d700c4fb")
    }
  },
  {
    "$lookup": {
      "from": "team",
      "localField": "teams.teamId",
      "foreignField": "_id",
      "as": "teams"
    }
  },
  {
    "$unwind": "$teams"
  },
  {
    "$replaceRoot": {
      "newRoot": "$teams"
    }
  },
  {
    $sort: {
      _id: 1
    }
  },
  {
    "$facet": {
      "content": [
        {
          $skip: <page number> * <page size>
          
        },
        {
          $limit: <page size>
          
        }
      ],
      "total": [
        {
          $group: {
            _id: null,
            total: {
              $sum: 1
            }
          }
        }
      ],
      "first": [
        {
          $limit: 1
        },
        {
          $project: {
            _id: 0,
            first: {
              $eq: [
                <page number>,
                0
              ]
              
            }
          }
        }
      ],
      "last": [
        {
          $skip: (<page number>+1) * <page size>
          
        },
        {
          $limit: 1
        }
      ]
    }
  },
  {
    "$project": {
      content: 1,
      first: {
        $first: "$first.first"
      },
      total: {
        $first: "$total.total"
      },
      last: {
        $eq: [
          "$last",
          []
        ]
      }
    }
  }
])

Mongo Playground