What is the Equivalent of $indexOfArray in C# Fluent Aggregate?

[Sudhesh_Gnanasekaran]

Below is the sample Mongo query to fetch the array elements based on the last modified date.

$arrayElemAt: [
                        '$Invoice',
                        {
                            $indexOfArray: [
                                '$Invoice.LastModifiedDateTime',
                                {
                                    $max: '$Payments.LastModifiedDateTime'
                                }
                            ]
                        }
                    ]

I tried the code in the following way

database.GetCollection<Invoice>(nameof(Invoice)).Aggregate()
				.Project(x => new ProjectResult
                      {
                         Payments = x.Payments == null ? null :  x.Payments.ElementAt(0)
                      });

Translated Query

{
  "$project": {
    "Payments": {
      "$cond": [
        {
          "$eq": [
            "$Payments",
            null
          ]
        },
        null,
        {
          "$arrayElemAt": [
            "$Payments",
            0
          ]
        }
      ]
    },
    "_id": 0
  }
}

How to implement the $indexOfArray in the code?

[James_Kovacs]

Hi, @Sudhesh_Gnanasekaran,

I understand that you’re trying to find a particular matching element of an array using $indexOfArray. There is no easy way to write this in C# as ElementAt takes an int32 and not a predicate. We would need to implement an extension method such as ElementMatching that could take a predicate and translate it into $arrayElemAt and $indexOfArray.

You could try something like this:

var query = coll.Aggregate()
    .Project(x => new ProjectResult
    {
        Payment = x.Payments == null ? null :  x.Payments.First(y => y.Date == x.Payments.Max(y => y.Date))
    });
Console.WriteLine(query);

Output is:

aggregate([{ "$project" : { "Payment" : { "$cond" : { "if" : { "$eq" : ["$Payments", null] }, "then" : null, "else" : { "$arrayElemAt" : [{ "$filter" : { "input" : "$Payments", "as" : "y", "cond" : { "$eq" : ["$$y.Date", { "$max" : "$Payments.Date" }] } } }, 0] } } }, "_id" : 0 } }])

Alternatively CSHARP-3958 will allow you to sort arrays using the new $sortArray operator introduced in MongoDB 5.2. You could then sort by LastModifiedDateTime descending and take the first element. CSHARP-3958 is still a work in progress, but will be available in an upcoming release.

Sincerely,
James

[Sudhesh_Gnanasekaran]

Thank you @James_Kovacs and for the update of CSHARP-3958

[Sudhesh_Gnanasekaran]

@James_Kovacs

var query = coll.Aggregate()
    .Project(x => new ProjectResult
    {
        Payment = x.Payments == null ? null :  x.Payments.First(y => y.Date == x.Payments.Max(y => y.Date))
    });

Hi @James_Kovacs

The expected translated query is wrong

In the below query “$Payments.Date” should come instead “$$y.Payments.Date” is replaced. Is this an existing bug or is any workaround available?

aggregate([{ “$project” : { “Payment” : { “$cond” : { “if” : { “$eq” : ["$Payments", null] }, “then” : null, “else” : { “$arrayElemAt” : [{ “$filter” : { “input” : “$Payments”, “as” : “y”, “cond” : { “$eq” : ["$$y.Date", { “$max” : “$$y.Payments.Date” }] } } }, 0] } } }, “_id” : 0 } }])

Thanks,
Sudhesh

[James_Kovacs]

Hi, @Sudhesh_Gnanasekaran,

In MQL. $field is equivalent to $$CURRENT.field. (See $$CURRENT.) Thus {$max: "$Payments.Date"} is equivalent to {$max: "$$CURRENT.Payments.Date"} and since $$y is the same as $$CURRENT, the two formulations are the same.

Inserting some test data and running the MQL in the mongo shell, we see the expected result returned.

> db.remittance.insert({Payments: [{Date: ISODate("2020-01-01")}, {Date: ISODate("2021-01-01")}, {Date: ISODate("2000-01-01")}]})
WriteResult({ "nInserted" : 1 })
> db.remittance.aggregate([{ "$project" : { "Payment" : { "$cond" : { "if" : { "$eq" : ["$Payments", null] }, "then" : null, "else" : { "$arrayElemAt" : [{ "$filter" : { "input" : "$Payments", "as" : "y", "cond" : { "$eq" : ["$$y.Date", { "$max" : "$Payments.Date" }] } } }, 0] } } }, "_id" : 0 } }])
{ "Payment" : { "Date" : ISODate("2021-01-01T00:00:00Z") } }

If you have a test case that does not produce the expected result, please consider filing a CSHARP or SERVER ticket so that we can investigate further.

Sincerely,
James