CODEWITHSUNDEEP
mongodbsortingaggregation-frameworkgrouping
DummyBeginner
DummyBeginner

Reputation: 431

Mongo aggregation - Sorting using a field value from previous pipeline as the sort field

I have produced the below output using mongodb aggregation (including $group pipeline inside levelsCount field) :

{
    "_id" : "1",
    "name" : "First",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 1 },
        { "_id" : "level_Three", "levelNum" : 3, "count" : 1 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 8 }
    ]
}
{
    "_id" : "2",
    "name" : "Second",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 5 },
        { "_id" : "level_Two", "levelNum" : 2, "count" : 2 },
        { "_id" : "level_Three", "levelNum" : 3, "count" : 1 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 3 }
    ]
}
{
    "_id" : "3",
    "name" : "Third",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 1 },
        { "_id" : "level_Two", "levelNum" : 2, "count" : 3 },
        { "_id" : "level_Three", "levelNum" : 3, "count" : 2 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 3 }
    ]
}

Now, I need to sort these documents based on the levelNum and count fields of levelsCount array elements. I.e. If two documents both had the count 5 forlevelNum: 1 (level_One), then the sort goes to compare the count of levelNum: 2 (level_Two) field and so on.

I see how $sort pipeline would work on multiple fields (Something like { $sort : { level_One : 1, level_Two: 1 } }), But the problem is how to access those values of levelNum of each array element and set that value as a field name to do sorting on that. (I couldn't handle it even after $unwinding the levelsCount array).

P.s: The initial order of levelsCount array's elements may differ on each document and is not important.

Edit: The expected output of the above structure would be:

// Sorted result:
{
    "_id" : "2",
    "name" : "Second",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 5 }, // "level_One's count: 5"  is greater than "level_One's count: 1" in two other documents, regardless of other level_* fields. Therefore this whole document with "name: Second" is ordered first.
        { "_id" : "level_Two", "levelNum" : 2, "count" : 2 },
        { "_id" : "level_Three", "levelNum" : 3, "count" : 1 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 3 }
    ]
}
{
    "_id" : "3",
    "name" : "Third",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 1 },
        { "_id" : "level_Two", "levelNum" : 2, "count" : 3 }, // "level_Two's count" in this document exists with value (3) while the "level_Two" doesn't exist in the below document which mean (0) value for count. So this document with "name: Third" is ordered higher than the below document.
        { "_id" : "level_Three", "levelNum" : 3, "count" : 2 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 3 }
    ]
}
{
    "_id" : "1",
    "name" : "First",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 1 },
        { "_id" : "level_Three", "levelNum" : 3, "count" : 1 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 8 }
    ]
}

Of course, I'd prefer to have an output document in the below format, But the first problem is to sort all docs:

{
    "_id" : "1",
    "name" : "First",
    "levelsCount" : [
        { "level_One" : 1 },
        { "level_Three" : 1 },
        { "level_Four" : 8 }
    ]
}

Upvotes: 2

Views: 2056

Answers (1)

turivishal
turivishal

Reputation: 36114

You can sort by levelNum as descending order and count as ascending order,

db.collection.aggregate([
  {
    $sort: {
      "levelsCount.levelNum": -1,
      "levelsCount.count": 1
    }
  }
])

Playground

For key-value format result of levelsCount array,

  • $map to iterate loop of levelsCount array
  • prepare key-value pair array and convert to object using $arrayToObject
  {
    $addFields: {
      levelsCount: {
        $map: {
          input: "$levelsCount",
          in: {
            $arrayToObject: [
              [{ k: "$$this._id", v: "$$this.levelNum" }]
            ]
          }
        }
      }
    }
  }

Playground

Upvotes: 2

Related Questions