CODEWITHSUNDEEP
javascriptnode.jsmongodb
user3142695
user3142695

Reputation: 17332

get the latest document after $group by

I'm using the official MongoDB driver for Node.js.

And this is how my message data is structured. As you can see, every post has a timestamp, an userId and the id of the topic.

[
  {
    "_id" : ObjectId("5b0abb48b20c1b4b92365145"),
    "topicId" : "XN7iqmCFD4jpgJZ6f",
    "timestamp" : 1527429960,
    "user" : "5b0869636f4e363e300d105a",
    "content" : "lorem ipsum"
  }    
]

Now I need to check if there are topics, which newest post (=highest timestamp) doesn't match my own ID. With that I do know which topic has a new answer (which is not my own post).

So I started with this:

db.messages.find({
  $query: { 
    user: { $ne: "myUserId" }
  },
  $orderby: { 
   timestamp: -1 
  }
}).limit(1).toArray()

My problem is, that I do not know how to group my query by the topicId. And somehow there seems to be wrong syntax in my attempt.

Upvotes: 2

Views: 42

Answers (2)

Ashh
Ashh

Reputation: 46441

You have to use aggregate to group by topicId and then $sort for sorting according to time and then $limit to limit the query

db.messages.aggregate([
    { $match: { user: { $ne: "myUserId" } }},
    { $group: {
        _id: "$topicId",
        timestamp: { $first: "$timestamp"},
        user: { $first: "$user" },
        content: { $first: "$content" }
    }},
    { $sort: { timestamp: -1 } },
    { $limit: 1 }
])

Upvotes: 1

Sanjay Vemuri
Sanjay Vemuri

Reputation: 32

Use aggregate pipeline to do this instead of find.

1) $match $ne :"myUserId"

2) $sort timestamp:-1

3) $group topicId $first

A sample query I have written in the past..

{ "$match": { $and: [ {'latestTimeStamp': { "$gte": new Date('3/11/2018') }}, {'latestTimeStamp': { "$lte": new Date('4/12/2018') }} ]}  },


                            { $unwind: '$latestSev'},


                            { $sort: {'latestSev.sevRating': -1}},


                            { $group:{  _id:{_id:'$_id', latestTimeStamp:'$latestTimeStamp', latestLikeli:'$latestLikeli', latestControl:'$latestControl', residualRatingArray:'$residualRatingArray'}, 


                                        latestMaxImpName: {$first:'$latestSev.impName'} ,

                                        latestMaxSevRating: {$first:'$latestSev.sevRating'}
                                    }

                            }

Upvotes: 0

Related Questions