Home > database >  Getting single document position in MongoDB aggregate
Getting single document position in MongoDB aggregate

Time:05-17

I am having troubles with what seems to be a simple problem in MongoDB but I haven't had much luck searching online.

I have a collection that looks like this:

{ "id" : 'a18qv5k', "name" : "Joe", "myArray" : ['foo']}
{ "id" : 'cbuesvhg', "name" : "Adam", "myArray" : ['foo', 'bar', 'sample text']}
{ "id" : 'h106pw97', "name" : "Bill", "myArray" : ['bar', 'lorem ipsum']}

I want to run an aggregation query to return the entire collection, sorted by the number of elements contained in myArray. I've already found a way to do that:

const aggregation = [{
    $project: {
        id: 0,
        name: 1,
        dim: {
            $size: '$myArray'
        }
    }
}, {
    $sort: {
        dim: -1
    }
}];

The next step (with which I am struggling) would be adding a position attribute to each projected document, showing its position in the returned collection. The result would look like:

{ "name" : "Adam", dim : 3, position: 1}
{ "name" : "Bill", dim: 2, position: 2}
{ "name" : "Joe", dim: 1, position: 3}

CodePudding user response:

Query

  • $setWindowFields can do it, requires >= MongoDB 5
  • here $rank is used and each document will have diffrent rank even if same array size, in case you want the same array size to have the same rank also you can use $denseRank instead of $rank

Playmongo

aggregate(
[{"$set": {"size": {"$size": "$myArray"}}},
 {"$setWindowFields": 
   {"sortBy": {"size": -1},
    "output": {"position": {"$rank": {}}}}}])
  • Related