MongoDB C# - How to do sorting and pagination with an array of objects on a record

Question

I have a MongoDB with a collection: people.

[
  {
    _id: 0,
    name: "lucas",
    properties: [
      {
        Name: "powers",
        Values: [
          "fireball",
          "icebeam",
          "thunderbolt"
        ]
      },
      {
        Name: "weakness",
        Values: [
          "poison",
          "rain"
        ]
      }
    ]
  },
  {
    _id: 1,
    name: "cage",
    properties: [
      {
        Name: "powers",
        Values: [
          "flight",
          "strength",
          "fireball"
        ]
      },
      {
        Name: "weakness",
        Values: [
          "lightning",
          "ice"
        ]
      }
    ]
  },
  {
    _id: 2,
    name: "joe",
    properties: [
      {
        Name: "powers",
        Values: [
          "immortality",
          "strength",
          "flight"
        ]
      }
    ]
  },
  {
    _id: 3,
    name: "bob",
    properties: [
      {
        Name: "weakness",
        Values: [
          "cold",
          "flu",
          "no food"
        ]
      }
    ]
  }
]

I purposely left off the "weakness" property for "joe" and "powers" from "bob", since not all people have weaknesses or powers. I'm unsure how to sort all records based on properties "powers" by their values. Assuming I have 50 heroes, I want to return all records but sorted by the properties and return it paginated.

Answer 1

In MongoDB query, you need to extract the powers array and its size (powersLength) before performing sorting.

After that, remove both fields so that they are not displayed in the result.

db.collection.aggregate([
  {
    $set: {
      properties: {
        $map: {
          input: "$properties",
          in: {
            $mergeObjects: [
              "$$this",
              {
                Values: {
                  $sortArray: {
                    input: "$$this.Values",
                    sortBy: 1
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $set: {
      powers: {
        $getField: {
          field: "Values",
          input: {
            $first: {
              $filter: {
                input: "$properties",
                cond: {
                  $eq: [
                    "$$this.Name",
                    "powers"
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $set: {
      powersLength: {
        $size: {
          $ifNull: [
            "$powers",
            []
          ]
        }
      }
    }
  },
  {
    $sort: {
      powersLength: -1,
      powers: 1
    }
  },
  {
    $unset: [
      "powers",
      "powersLength"
    ]
  }
])

Demo @ Mongo Playground

---

In MongoDB .NET Driver syntax, I think this is complex and possibly not achievable with full Fluent API syntax.

But you can translate the query into BsonDocument via MongoDB Compass (Export to Language feature).

var pipeline = new BsonDocument[]
{
    new BsonDocument("$set",
    new BsonDocument("powers",
    new BsonDocument("$getField",
    new BsonDocument
                {
                    { "field", "Values" },
                    { "input",
    new BsonDocument("$first",
    new BsonDocument("$filter",
    new BsonDocument
                            {
                                { "input", "$properties" },
                                { "cond",
    new BsonDocument("$eq",
    new BsonArray
                                    {
                                        "$$this.Name",
                                        "powers"
                                    }) }
                            })) }
                }))),
    new BsonDocument("$set",
    new BsonDocument("powersLength",
    new BsonDocument("$size",
    new BsonDocument("$ifNull",
    new BsonArray
                    {
                        "$powers",
                        new BsonArray()
                    })))),
    new BsonDocument("$sort",
    new BsonDocument
        {
            { "powersLength", -1 },
            { "powers", 1 }
        }),
    new BsonDocument("$unset",
    new BsonArray
        {
            "powers",
            "powersLength"
        })
};

var result = await _col.Aggregate<People>(pipeline)
    .ToListAsync();

Or work with the Fluent API partially:

var setStageFirst = new BsonDocument("$set",
    new BsonDocument("powers",
        new BsonDocument("$getField",
            new BsonDocument
            {
                { "field", "Values" },
                { "input",
                    new BsonDocument("$first",
                        new BsonDocument("$filter",
                            new BsonDocument
                            {
                                { "input", "$properties" },
                                { "cond",
                                    new BsonDocument("$eq",
                                        new BsonArray
                                        {
                                            "$$this.Name",
                                            "powers"
                                        })
                                }
                            }))
                }
            })));

var setStageSecond = new BsonDocument("$set",
    new BsonDocument("powers",
        new BsonDocument("$getField",
            new BsonDocument
            {
                { "field", "Values" },
                { "input",
                    new BsonDocument("$first",
                        new BsonDocument("$filter",
                            new BsonDocument
                            {
                                { "input", "$properties" },
                                { "cond",
                                    new BsonDocument("$eq",
                                        new BsonArray
                                        {
                                            "$$this.Name",
                                            "powers"
                                        }) 
                                }
                            })) 
                }
            })));

var setStageThird = new BsonDocument("$set",
    new BsonDocument("powersLength",
        new BsonDocument("$size",
            new BsonDocument("$ifNull",
                new BsonArray
                {
                    "$powers",
                    new BsonArray()
                }))));

var sortStage = new BsonDocument
        {
            { "powersLength", -1 },
            { "powers", 1 }
        };

var unsetStage = new BsonDocument("$unset",
    new BsonArray
        {
            "powers",
            "powersLength"
        });

var result = await _col.Aggregate()
    .AppendStage<BsonDocument>(setStageFirst)
    .AppendStage<BsonDocument>(setStageSecond)
    .AppendStage<BsonDocument>(setStageThird)
    .Sort(sortStage)
    .AppendStage<People>(unsetStage)
    .ToListAsync();