CODEWITHSUNDEEP
mongodbmongoose
Sean Goudarzi
Sean Goudarzi

Reputation: 1574

MongoDB find only the newest combination of fields

I have 3 collections:

1:

{
"_id": {
    "$oid": "5f37dad305c1b9403bfe808b"
},
"nextBilling": {
    "isAutomatic": true,
    "isRedeemedForFutureBilling": false,
    "time": 1000,
    "duration": 1000
},
"userToken": "aaaa",
"__v": 0
}

2:

{
"_id": {
    "$oid": "5f37dad305c1b9403bfe323b"
},
"nextBilling": {
    "isAutomatic": true,
    "isRedeemedForFutureBilling": false,
    "time": 2000,
    "duration": 1000
},
"userToken": "aaaa",
"__v": 0
}

3:

{
"_id": {
    "$oid": "5f37dfj1b9403bfe323b"
},
"nextBilling": {
    "isAutomatic": true,
    "isRedeemedForFutureBilling": false,
    "time": 1000,
    "duration": 1000
},
"userToken": "bbbb",
"__v": 0
}

These are payments from users, and I want to get every users' payments, but only and only their last payment (which can be determined by nextBilling.time). So what I want to get at the very last is collections number 2 and 3. Users are identified by the userToken, so what I actually want is all combinations of (userToken and nextBilling.time) where nextBilling.time is the largest and only one result should be returned for each userToken.

What i'd do in mySql for achieving this would be sth like:

SELECT *  FROM payments WHERE (user_token, next_billing_time) IN (SELECT user_token, MAX(ts_in_sec) FROM payments group by user_token)

I'm a bit lost about what I should do in mongoDB for this. I'm using mongoose.

Upvotes: 1

Views: 44

Answers (2)

turivishal
turivishal

Reputation: 36134

You can try using aggregate(),

  • $sort by nextBilling.time descending order
db.collection.aggregate([
  { $sort: { "nextBilling.time": -1 } },
  • $group by userToken and set first nextBilling and id
  {
    $group: {
      _id: "$userToken",
      id: { $first: "$_id" },
      nextBilling: { $first: "$nextBilling" }
    }
  },

  {
    $project: {
      _id: "$id",
      userToken: "$_id",
      nextBilling: 1
    }
  }
])

Playground

Upvotes: 1

Navitas28
Navitas28

Reputation: 777

You can do :

const result = await ModelName.aggregate([
    {$group: {_id: '$userToken', "time": {$max: "$nextBilling.time"}}}
])

Upvotes: 1

Related Questions