Mongoose Query Help: Paginate, Sort, Limit on Nested Array

Question

I am have a chat Mongoose model in the below is the sample data. If this is still not clear please revert back to me with your questions. Any help is greatly appreciated.

        { 
            "_id" : ObjectId("5745910831a1sd58d070a8faa"), 
            "messages" : [
                {
                    "user" : "user1", 
                    "message" : "How are you user1?", 
                    "readInd" : "N", 
                    "createDate" : ISODate("2016-05-25T11:36:00.468+0000"), 
                    "_id" : ObjectId("5745912c31a1c58d070a904d")
                }, 
                {
                    "user" : "user1", 
                    "message" : "Hello user1", 
                    "readInd" : "N", 
                    "createDate" : ISODate("2016-05-25T11:38:53.893+0000"), 
                    "_id" : ObjectId("5745912531a1c58d070a902e")
                }
            ], 
            "createDate" : ISODate("2016-05-25T11:35:20.534+0000"), 
            "users" : [
                "57450b4506561ff5052f0a66", 
                "57450d8108d8d22c06cf138f"
            ], 
            "__v" : NumberInt(0)
        },
        { 
            "_id" : ObjectId("57458e9331a1c58d070a8e30"), 
            "messages" : [
                {
                    "user" : "user2", 
                    "message" : "How are you user2", 
                    "readInd" : "N", 
                    "createDate" : ISODate("2016-05-25T11:46:03.240+0000"), 
                    "_id" : ObjectId("574590f331a1c58d070a8ede")
                }, 
                {
                    "user" : "user2", 
                    "message" : "Hello user2", 
                    "readInd" : "N", 
                    "createDate" : ISODate("2016-05-25T11:48:53.925+0000"), 
                    "_id" : ObjectId("574590e931a1c58d070a8eab")
                }
            ], 
            "createDate" : ISODate("2016-05-25T11:35:20.534+0000"), 
            "users" : [
                "5745149e3aaab38706c00b64", 
                "57450d8108d8d22c06cf138f"
            ], 
            "__v" : NumberInt(0)
        }
        { 
            "_id" : ObjectId("5745910831a1c58d070a8faa"), 
            "messages" : [
                {
                    "user" : "user3", 
                    "message" : "How are you user3?", 
                    "readInd" : "N", 
                    "createDate" : ISODate("2016-05-25T11:56:00.468+0000"), 
                    "_id" : ObjectId("5745912c31a1c58d070a904d")
                }, 
                {
                    "user" : "user3", 
                    "message" : "Hello user3", 
                    "readInd" : "N", 
                    "createDate" : ISODate("2016-05-25T11:58:53.893+0000"), 
                    "_id" : ObjectId("5745912531a1c58d070a902e")
                }
            ], 
            "createDate" : ISODate("2016-05-25T11:35:20.534+0000"), 
            "users" : [
                "57450b4506561ff5052f0a66", 
                "57450d8108d8d22c06cf138f"
            ], 
            "__v" : NumberInt(0)
        },
        { 
            "_id" : ObjectId("5745910831a1c58d070a8faa"), 
            "messages" : [
                {
                    "user" : "user4", 
                    "message" : "How are you user4?", 
                    "readInd" : "N", 
                    "createDate" : ISODate("2016-05-25T11:66:00.468+0000"), 
                    "_id" : ObjectId("5745912c31a1c58d070a904d")
                }, 
                {
                    "user" : "user4", 
                    "message" : "Hello user4", 
                    "readInd" : "N", 
                    "createDate" : ISODate("2016-05-25T11:68:53.893+0000"), 
                    "_id" : ObjectId("5745912531a1c58d070a902e")
                }
            ], 
            "createDate" : ISODate("2016-05-25T11:35:20.534+0000"), 
            "users" : [
                "57450b4506561ff5052f0a66", 
                "57450d8108d8d22c06cf138f"
            ], 
            "__v" : NumberInt(0)
        }

below is the explanation:

user1 sent 2 messages at 11:36 and 11:38 respectively
user2 sent 2 messages at 11:46 and 11:48 respectively
user3 sent 2 messages at 11:56 and 11:58 respectively
user4 sent 2 messages at 11:66 and 11:68 respectively

My Expected Result is:

Pagination/limit Criteria:

show 2 records per page.
show only the Most recent message based on user.

Sample output:

Page1:

   "user" : "57450d8108d8d22c06cf138f",              
   "message" : "How are you user4?"

   "user" : "57450d8108d8d22c06cf138f", 
   "message" : "How are you user3?"

Page2:

   "user" : "57450d8108d8d22c06cf138f", 
   "message" : "How are you user2"

   "user" : "57450d8108d8d22c06cf138f", 
   "message" : "How are you user1?"

score 2 · Accepted Answer · answered May 25 '16 at 13:01

2

try this way of query , this is help to u

query for page one

db.getCollection('message').aggregate( [ { $match : { user : "57450d8108d8d22c06cf138f" } },
{ $unwind : "$messages" } ,
{ $sort : { 'messages.createDate' : -1} },
 { $limit : 2 },
 { $project : { _id: 0,'message':'$messages.message','user':'$messages.user'} } ])

query for next page

db.getCollection('message').aggregate( [ { $match : { user : "57450d8108d8d22c06cf138f" } },
    { $unwind : "$messages" } ,
    { $sort : { 'messages.createDate' : -1} },
     { $limit : 2 },{ $skip : 2 }
     { $project : { _id: 0,'message':'$messages.message','user':'$messages.user'} } ])

answered May 25 '16 at 13:01

karthi

880
6
10

Thanks for your feedback. a small change on the next page. the order of $limit and $skip should be changed so that $skip comes first and $limit comes second. {$skip:1}, {$limit:1} – user2596892 May 25 '16 at 14:08
ya its right . i just post the ans but i dint check that – karthi May 25 '16 at 16:14
Can someone please answer how to do the same thing using mongoose? – logdev Jun 18 '19 at 21:55
@SyedSouban I hope this link will help you http://excellencenodejsblog.com/mongoose-aggregation-count-group-match-project/ , no need to change anything in query, just create a model for collection then use the model name instead of putting this "db.getCollection(message)" – karthi Jun 19 '19 at 12:37

Mongoose Query Help: Paginate, Sort, Limit on Nested Array

1 Answers1