Get paginated records and total records in one mongodb aggregate query-CodePudding

I have my monogdb departments data structure like as shown below

[{
    category: "ABC",
    sections: [
        {
            section_hod: "x111",
            section_name: "SECTION A",
            section_staff_count: "v11111",
            section_id: "a1111",
            :
        },
        {
            section_hod: "x2222",
            section_name: "SECTION B",
            section_staff_count: "v2222",
            section_id: "a2222",
            :
        }
    ]
}
:
:
]

I wrote a mongodb query like as shown below

    db.getSiblingDB("departments").getCollection("DepartmentDetails").aggregate([
        { $unwind : "$sections"},
        { $match : { $and : [{ "sections.section_name" : "SECTION A"},
                    { $or : [{ "category" : "ABC"}]}]}},
        {$project : { "name" : "$sections.section_name", "hod" : "$sections.section_hod", "staff_count" : "$sections.section_staff_count", "id" : "$sections.section_id"}},
{$skip: 0}, {$limit: 10}
    ]);

which gives me a list of section details as shown below which contains name, hod, staff_count, id etc

[
    {
      "name": "xxxxx",
      "hod": "xxxxx",
      "staff_count": "xxxxx",
      "id": "xxxxx"
    },
    {
      "name": "yyyyy",
      "hod": "yyyyy",
      "staff_count": "yyyyy",
      "id": "yyyyy"
    }
    :
    :
    :
]

Everything looks good, but the problem is I have so many records in the list with which I am trying to build a pagination. For implementing pagination I know I can use the skip and limit function for iterating the pages, but for doing that I need to know the total counts of all the records.

I can do this in two ways, First way is I can execute two queries one which will be a count and then the aggregate query passing the skip and limit, second way is execute one query which return me the total counts and the documents in the order of first paginated page.

I am trying to implement the second way and bring the expected result is as shown below

{
  "documents": [
    {
      "name": "xxxxx",
      "hod": "xxxxx",
      "staff_count": "xxxxx",
      "id": "xxxxx"
    },
    {
      "name": "yyyyy",
      "hod": "yyyyy",
      "staff_count": "yyyyy",
      "id": "yyyyy"
    }
    :
    :
    :
  ],
  "totalCount": 5444
}

Not sure if this is achievable. Can someone please help me on this. My default limit is 10

CodePudding user response：

You can do it like this, it will give you total records and paginated results in one go,

db.getSiblingDB("departments").getCollection("DepartmentDetails")
    .aggregate([
        { $unwind : "$sections"},
        { $match : { $and : [{ "sections.section_name" : "SECTION A"},
        { $or : [{ "category" : "ABC"}]}]}},
        { 
            $project : { 
                "name" : "$sections.section_name", 
                "hod" : "$sections.section_hod", 
                "staff_count" : "$sections.section_staff_count", 
                "id" : "$sections.section_id"
            }    
        },
        {
            $facet: {
                metaData: [{
                    $count: 'total'
                }],
                records: [
                    {$skip: 0}, 
                    {$limit: 10}
                ]
            }
        },
        {
            $project: {
                records: 1,
                total: {
                    $let: {
                        vars: {
                            totalObj: { 
                                $arrayElemAt: ['$metaData', 0]           
                            }
                        },
                    in: '$$totalObj.total'
                    }
                },
            }
        }
    ]);