MongoDB Aggregation: How can I apply pagination in MongoDB aggregation

Question

This is my query, everything is working fine but don't know how to apply pagination while using aggregate :

try {
        let data =[
            {
            $match : {
                $or : [{'firstName' : {$regex : '.*' + req.body.searchKey + '.*', '$options' : 'i'}},
                {'lastName' : {$regex : '.*' + req.body.searchKey + '.*', '$options' : 'i'}}]
            }
          },{
            $lookup : {
             from : 'prescriptions',
             let: { patientId: '$_id' },

             pipeline: [ 
               {
                 $match: {  
                   $expr: {  $eq: ['$patientId','$$patientId']}
                 }       
                },  {           
                    $lookup : {
                     from : 'users',
                     let: { doctorId: '$doctorId' },
                     pipeline: [
                       {
                         $match: {  
                           $expr: {  $eq: ['$_id','$$doctorId']}
                         }       
                        },     
                     ],
                     as : 'doctorData'              
                    }
                }
             ],
             as : 'patientData'    
            },
           }

          ]

          let data1 = await userModel.aggregate(data);
          res.status(200).json({success : true, data : data1});

    }

I want to apply pagination on this query. I'm using aggregation for the first time. Please help, thanks in advance.

You want to paginate on `users` list or `prescriptions` list ? — Sarfraaz, Jan 29 '20 at 04:50
It would be helpful if you can share the data model of users (patient) and prescriptions. — Jeet, Jan 29 '20 at 05:03

Sarfraaz · Accepted Answer · 2020-01-29T05:02:13.733

You can do it like this

let page_no = 0;
let limit = 10;
let offset = page_no > 1 ? (page_no-1)*limit : 0;

let data = [
    {
        $match: {
            $or: [
                { firstName: { $regex: '.*' + req.body.searchKey + '.*', $options: 'i' } },
                { lastName: { $regex: '.*' + req.body.searchKey + '.*', $options: 'i' } }
            ]
        }
    },{
        $skip: offset
    },{
        $limit: limit
    },
    {
        $lookup: {
            from: 'prescriptions',
            let: { patientId: '$_id' },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ['$patientId', '$$patientId'] }
                    }
                },
                {
                    $lookup: {
                        from: 'users',
                        let: { doctorId: '$doctorId' },
                        pipeline: [
                            {
                                $match: {
                                    $expr: { $eq: ['$_id', '$$doctorId'] }
                                }
                            }
                        ],
                        as: 'doctorData'
                    }
                }
            ],
            as: 'patientData'
        }
    }
];

let data1 = await userModel.aggregate(data);

let total = await userModel.find({
        $or: [
            { firstName: { $regex: '.*' + req.body.searchKey + '.*', $options: 'i' } },
            { lastName: { $regex: '.*' + req.body.searchKey + '.*', $options: 'i' } }
        ]
    }).countDocuments();

let is_next = offset+limit < total;
res.status(200).json({ success: true, data: data1, is_next:is_next });

Here, we get page_no from 0 to N for data of particular page,

Skip page_no*limit records first & then limit records to return in aggregation

We get total_no of records for query & match it with offset+limit, to see if next page exist or not

this is one approach I do,

For other approach, we can return total no of pages & return same in response like this

    let total = await userModel.find({
        $or: [
            { firstName: { $regex: '.*' + req.body.searchKey + '.*', $options: 'i' } },
            { lastName: { $regex: '.*' + req.body.searchKey + '.*', $options: 'i' } }
        ]
    }).countDocuments();

    let pages = Math.ceil(total/limit);
res.status(200).json({ success: true, data: data1, no_of_pages:pages });

Here the pagination would only be applied on users or rather patient i guess the OP wants pagination on final result. The problem what I can foresee is if the user patient data is large, it will break the query and run over the limit hence pagination would break. — Jeet, Jan 29 '20 at 04:59
without getting total count of records how we can find total number of pages — padam, Jan 29 '20 at 05:00
Check, I have updated the answer, two approaches to get pagination — Sarfraaz, Jan 29 '20 at 05:07

MongoDB Aggregation: How can I apply pagination in MongoDB aggregation

1 Answers1