How to group and pick the first record by multiple queries?-CodePudding

sorry for my English if my question here is not clear for you because I can't state my problem in a clearer way.

TLDR:

Need to group by fieldA
sort decreasing by fieldB
and then sort increasing by fieldC
then pick only the top one of each group.

Here is my scenario; I have some records in mongodb. A sample document:

{ "userId": 2, "data": "bbbbbx", version: 2, mainVersion: 1 }

A user may have multiple records, e.g. userId:2 has N records. rule is:anytime a new record is saved here, version attribute is incremented by 1.

So, userId may have 5 records; oldest record's version is 1, latest record's version is 5.

Also, these records are inherited from a main record of the system, which also has a version number. The record above has a "mainVersion" field, which is 1. When system's main record is updated, user records are updated as well.

Let's say that; userId:2 had 5 records, most recent one's version is 5 and mainVersion is 1. Then a system update occurs, all user records are updated as well. UserId:2 now has 6 records, I can show the flow of records of userId:2 like that:

version:1 mainVersion:1
(user updates its record)
version:2 mainVersion:1
(user updates its record)
version:3 mainVersion:1
(user updates its record)
version:4 mainVersion:1
(user updates its record)
version:5 mainVersion:1
(System update here!)
version:5 mainVersion:2

If a new user is added to the system, its first record starts with the current mainVersion. If userId:3 is the new user at this point, its first record should be:

{ "userId": 3, "data": "data here", version: 1 (its first record) , mainVersion: 2 (current mainVersion) }

My problem: I am trying to make some queries.

I was trying to pick the most recent records for user's. For the scenario I wrote, I need the record;

version:5 mainVersion:2

for userId:2. Main sort field is mainVersion, secondary sort field is version. If userId:2 has created a record earlier with version:6 but mainVersion:1 (We can say its a system malfunction or the user decided not to use that record) I ignore it.

I have implemented this basic scenario in mongo playground, you can check this. https://mongoplayground.net/p/LMBpWSLB1tN

I think I am able to pick the most recent record with my aggregation query there, I use mergeObjects with double fields, picking the max of mainVersion and then version.

But now I need to pick the first records of the current mainVersion. Lets say that userId:2's record I want is:

version:5 mainVersion:2

But userId:3 has many updates and has N records with mainVersion:2.

version:1 mainVersion:1
version:2 mainVersion:1
...
version:11 mainVersion:1
version:11 mainVersion:2 (current mainVersion's first record, I need that one!)
version:12 mainVersion:2
...
version:N mainVersion:2 (this is the most recent one)

I need the record with version:11 and mainVersion:2 for userId:3.

I need that for every userId each.

The mongo here I work on is version 4.2.22 so I can't use newer stuff like window functions etc. introduced in newer versions.

Any ideas to pick these records? I don't know how to use more than one mergeObjects with getting the max mainVersion and min version. (Sorry for the long post)

CodePudding user response：

You can just perform your $sort $limit logic inside a sub-pipeline in $lookup

db.collection.aggregate([
  {
    $group: {
      _id: "$userId"
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "let": {
        uid: "$_id"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $eq: [
                "$$uid",
                "$userId"
              ]
            }
          }
        },
        {
          $sort: {
            mainVersion: -1,
            version: 1
          }
        },
        {
          "$limit": 1
        }
      ],
      "as": "top1"
    }
  },
  {
    $unwind: "$top1"
  },
  {
    "$replaceRoot": {
      "newRoot": "$top1"
    }
  }
])

Mongo Playground