Sorry if the title is wrong but here is what i am trying to do, let's say i have the objects :

{"_id": "anyID0", "contentID": "content1", "value": "any value", "at": 10} 
{"_id": "anyID1", "contentID": "content1", "value": "any value", "at": 9} 
{"_id": "anyID2", "contentID": "content2", "value": "any value", "at": 8} 
{"_id": "anyID3", "contentID": "content3", "value": "any value", "at": 7}  

I would like to perform a query that :

• Get 20 of these objects using limit
• Get them sort by at descending
• If the query matches an already queried contentID, then skip it without considering it as a result.

As an example with the above objects : If i use query.limit(2) where query is the one i am asking here, i would like the result to be :

{"_id": "anyID0", "contentID": "content1", "value": "any value", "at": 10} 
{"_id": "anyID2", "contentID": "content2", "value": "any value", "at": 8} 

So it has skipped the {"_id": "anyID1", "contentID": "content1", "value": "any value", "at": 9}

I would like to know if it's possible without aggregation and / or if it would have correct performances.

I also would like to see working examples, thanks in advance !

CodePudding user response：

I would like to know if it's possible without aggregation and / or if it would have correct performances.

No it's not possible.

Unfortunately there is no efficient way to achieve this in Mongo. You'll have to first $group all documents by contentId, then we'll have to sort them again as $group does not preserve order.

At this point we'll be able to paginate with $skip and $limit, obviously as you can tell this is very inefficient.

db.collection.aggregate([
  {
    $group: {
      _id: "$contentID",
      first: {
        $first: "$$ROOT"
      }
    }
  },
  {
    $sort: {
      "first._id": 1
    }
  },
  {
    $limit: 2
  },
  {
    $replaceRoot: {
      newRoot: "$first"
    }
  }
])

Mongo Playground

If you'd be open to either a schema does or updating another collection this would be best, for example you could create another collection which will only contain the first document. this will allow easy pagination, it is also somewhat easy to maintain this on updates, but if you have a lot of deletions this approach is also not optimal and I'd recommend you go for a schema change.