C# MongoDB - Filtering nested array data

Question

i am new on MongoDB and i am trying to use it in C# context. Let´s say, i have documents like this:

[
    {
        "Number": "2140007529",
        "Name": "ABC",
        "IsInactive": true,
        "EntryList": [
            {
                "Timestamp": "2022-06-01T14:00:00.000+00:00",
                "Value": 21564.0
            },
            {
                "Timestamp": "2022-07-01T21:31:00.000+00:00",
                "Value": 21568.0
            },
            {
                "Timestamp": "2022-08-02T21:21:00.000+00:00",
                "Value": 21581.642
            },
            {
                "Timestamp": "2022-09-02T15:42:00.000+00:00",
                "Value": 21593.551
            },
            {
                "Timestamp": "2022-09-26T13:00:00.000+00:00",
                "Value": 21603
            }
        ]
    },
    {
        "Number": "2220000784",
        "Name": "XYZ",
                "IsInactive": false,
        "EntryList": [
            {
                "Timestamp": "2022-09-26T13:00:00.000+00:00",
                "Value": 0.0
            },
            {
                "Timestamp": "2022-10-01T08:49:00.000+00:00",
                "Value": 5.274
            },
            {
                "Timestamp": "2022-11-01T09:56:00.000+00:00",
                "Value": 76.753
            },
            {
                "Timestamp": "2022-12-01T19:43:00.000+00:00",
                "Value": 244.877
            },
            {
                "Timestamp": "2023-01-01T11:54:00.000+00:00",
                "Value": 528.56
            },
            {
                "Timestamp": "2023-02-01T17:21:00.000+00:00",
                "Value": 802.264
            }
        ]
    }
]

I want to get the document where the IsInactive flag is false. But for the EntryList there should be returned entries greater than Timestamp 2022-12-31 only.I should look like this:

{
        "Number": "2220000784",
        "Name": "XYZ",
                "IsInactive": false,
        "EntryList": [
            {
                "Timestamp": "2023-01-01T11:54:00.000+00:00",
                "Value": 528.56
            },
            {
                "Timestamp": "2023-02-01T17:21:00.000+00:00",
                "Value": 802.264
            }
        ]
    }

So, here is my question. How can i filter nested arrays in return value with C#. Thanks for help!

I tried to get the result with aggregate function of MongoDB in MongoDB Compass. I got it work with but not in C#.

Answer 1

I think you are looking for a query similar to this one.

So you can try something like this code:

var desiredTimestamp = new DateTime(2022, 12, 31);

var results = collection.AsQueryable()
    .Where(x => x.IsInactive == false && x.EntryList.Any(e => e.Timestamp >= desiredTimestamp))
    .Select(obj => new
    {
        Number = obj.Number,
        Name = obj.Name,
        IsInactive = obj.IsInactive,
        EntryList = obj.EntryList
            .Where(e => e.Timestamp >= desiredTimestamp)
            .ToList()
    }).ToList()

Note that I'm assuming your Timestamp is a date type, otherwise you can't compare date and string.