C# mongodb driver query nested array-CodePudding

I am struggling now since hours to get one query working. My shema looks like the following:

public class ActionResponse
{
    public string Id { get; set; }
    public List<ActionResponseItem> ResponseItems { get; set; }
}

public class ActionResponseItem
{
    public string Id { get; set; }
    public DateTime Created { get; set; }
    public List<string> Options { get; set; }
}

Let's assume I've following collection:

[
    {
    "_id": ObjectId("62bd901c4af4e9d2e1287984"),
    "ResponseItems": [
      {
        "_id": ObjectId("62bd9068b4598dd0bb07b536"),
        "Created": ISODate("2022-05-30T08:45:31.662Z"),
        "Options": [
          "Some string 1",
          "Some string 2"
        ]
      },
      {
        "_id": ObjectId("62bd906e35087e146b6b28f6"),
        "Created": ISODate("2022-05-30T08:46:56.192Z"),
        "Options": [
          "Some string 3",
          "Some string 4"
        ]
      }
    ]
  },
  {
    "_id": ObjectId("5e29560a2c4c55d421a4e1b4"),
    "ResponseItems": [
      {
        "_id": ObjectId("62bd62ab886b3fde8368ec39"),
        "Created": ISODate("2022-06-30T08:45:31.662Z"),
        "Options": [
          "Some string 1",
          "Some string 2"
        ]
      },
      {
        "_id": ObjectId("62bd6300886b3fde8368ec3f"),
        "Created": ISODate("2022-06-30T08:46:56.192Z"),
        "Options": [
          "Some string 3",
          "Some string 4"
        ]
      },
      {
        "_id": ObjectId("62bd6349886b3fde8368ec46"),
        "Created": ISODate("2022-06-30T08:48:09.226Z"),
        "Options": null
      },
      {
        "_id": ObjectId("62bd64a88f5b6b24b6de199e"),
        "Created": ISODate("2022-06-30T08:54:00.450Z"),
        "Options": null
      },
      {
        "_id": ObjectId("62bd64aa8f5b6b24b6de19a7"),
        "Created": ISODate("2022-06-30T08:54:02.767Z"),
        "Options": null
      },
      {
        "_id": ObjectId("62bd64af8f5b6b24b6de19b1"),
        "Created": ISODate("2022-06-30T08:54:07.896Z"),
        "Options": null
      },
      {
        "_id": ObjectId("62bd64b38f5b6b24b6de19bc"),
        "Created": ISODate("2022-06-30T08:54:11.837Z"),
        "Options": null
      },
      {
        "_id": ObjectId("62bd64b78f5b6b24b6de19c8"),
        "Created": ISODate("2022-06-30T08:54:15.588Z"),
        "Options": [
          "Some string 5",
          "Some string 6"
        ]
      },
      {
        "_id": ObjectId("62bd64bd8f5b6b24b6de19d5"),
        "Created": ISODate("2022-06-30T08:54:21.494Z"),
        "Options": [
          "Some string 7"
        ]
      },
      {
        "_id": ObjectId("62bd654d8f5b6b24b6de331d"),
        "Created": ISODate("2022-06-30T08:56:45.487Z"),
        "Options": null
      }
    ]
  }
]

I want to get a List where several conditions matches, e.g.: ActionResponse.Id = '5e29560a2c4c55d421a4e1b4' & ActionResponseItem.Created >= ISODate("2022-06-30T08:54:17Z") & ActionResponseItem.Created <= ISODate("2022-06-30T10:09:18.403Z") and return only the nested elements like:

[
    {
        "_id": ObjectId("62bd64b78f5b6b24b6de19c8"),
        "Created": ISODate("2022-06-30T08:54:15.588Z"),
        "Options": [
          "Some string 5",
          "Some string 6"
        ]
      },
      {
        "_id": ObjectId("62bd64bd8f5b6b24b6de19d5"),
        "Created": ISODate("2022-06-30T08:54:21.494Z"),
        "Options": [
          "Some string 7"
        ]
      },
      {
        "_id": ObjectId("62bd654d8f5b6b24b6de331d"),
        "Created": ISODate("2022-06-30T08:56:45.487Z"),
        "Options": null
      }
]

I've tried several queries (with/without aggregation, projection) and so on...

With this query only the first matching element is returned:

Mongo
db.collection.find({
  "_id": ObjectId("5e29560a2c4c55d421a4e1b4"),
  "ResponseItems": {
    "$elemMatch": {
      "Created": {
        "$gte": ISODate("2022-06-30T08:54:17Z"),
        "$lte": ISODate("2022-06-30T10:13:21.754Z")
      }
    }
  }
},
{
  "ResponseItems": {
    "$elemMatch": {
      "Created": {
        "$gte": ISODate("2022-06-30T08:54:17Z"),
        "$lte": ISODate("2022-06-30T10:13:21.754Z")
      }
    }
  }
})

C#
var filter = Builders<ActionResponse>.Filter.Eq(a => a.Id, id);
var nestedFilter =  Builders<ActionResponseItem>.Filter.Gte(x => x.Created, from) &
                    Builders<ActionResponseItem>.Filter.Lte(x => x.Created, to);
var elements = await _collection
            .Find(filter & Builders<ActionResponse>.Filter.ElemMatch(b => b.ResponseItems, nestedFilter))
            .Project(Builders<ActionResponse>.Projection
                .ElemMatch(c => c.ResponseItems, nestedFilter))
            .ToListAsync();

With this the whole document is returned:

Mongo
db.collection.aggregate([
  {
    "$match": {
      "_id": ObjectId("5e29560a2c4c55d421a4e1b4"),
      "ResponseItems": {
        "$elemMatch": {
          "Created": {
            "$gte": ISODate("2022-06-30T08:54:17Z"),
            "$lte": ISODate("2022-06-30T10:09:18.403Z")
          }
        }
      }
    }
  }
])

C#
var filter = Builders<ActionResponse>.Filter.Eq(a => a.Id, id);
var nestedFilter =  Builders<ActionResponseItem>.Filter.Gte(x => x.Created, from) &
                    Builders<ActionResponseItem>.Filter.Lte(x => x.Created, to);
var elements = await _collection.Aggregate()
            .Match(filter & Builders<ActionResponse>.Filter.ElemMatch(b => b.ResponseItems, nestedFilter))
            .ToListAsync();

Maybe there is only a small thing to change and it will work. I hope somebody can help me out on this problem.

MongoDb Playground

THX

CodePudding user response：

something like this should work, you need to do a match, unwind, match and then replace the root

using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
using MongoDB.Driver;

var client = new MongoClient();
var itemFilter =
    Builders<UnwindResponseItemsActionResponse>.Filter.Gte(x => x.ResponseItems.Created,
        new DateTime(2022, 06, 30, 08, 54, 17, DateTimeKind.Utc))
    & Builders<UnwindResponseItemsActionResponse>.Filter.Lte(x => x.ResponseItems.Created,
        new DateTime(2022, 06, 30, 10, 09, 18, 403, DateTimeKind.Utc));

var actionResponseItems = await client.GetDatabase("test")
    .GetCollection<ActionResponse>("actions")
    .Aggregate()
    .Match(Builders<ActionResponse>.Filter.Eq(x => x.Id, "5e29560a2c4c55d421a4e1b4"))
    .Unwind<ActionResponse, UnwindResponseItemsActionResponse>(x => x.ResponseItems)
    .Match(itemFilter)
    .ReplaceRoot(x => x.ResponseItems)
    .ToListAsync();

foreach (var item in actionResponseItems)
{
    Console.WriteLine(item.Id);
    Console.WriteLine(item.Created);
    Console.WriteLine();
}

/*
 *
62bd64bd8f5b6b24b6de19d5
30/06/2022 08:54:21

62bd654d8f5b6b24b6de331d
30/06/2022 08:56:45

 */

public class UnwindResponseItemsActionResponse
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id { get; set; }
    public ActionResponseItem ResponseItems { get; set; }
}


public class ActionResponse
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id { get; set; }
    public List<ActionResponseItem> ResponseItems { get; set; }
}

public class ActionResponseItem
{
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id { get; set; }
    public DateTime Created { get; set; }
    public List<string> Options { get; set; }
}```