MongoDB/C# How to query a deeply nested array only?

user_5451421 0
Viewed 1722

I'm getting tired that I can't figure out(nor alone nor through SO) how to pull out deeply nested data within BsonDocument or custom classes directly. I know that I should use for sure a filter and a projection to get out an array/list of Guids nested within another array. Following is the structure(simplified):

//Thread
{
  Id: "B2",
  Answers: [
    {
      Id: "A1",
      Likes: [ "GUID1", "GUID2", "ETC" ] //<- this array, and only this.
    }
  ]
}

I have both the Thread.Id and Answer.Id as filtering data but then I tried with:

var f = Builders<BsonDocument>.Filter;
var filter = f.And(f.Eq("Id", ids.ThreadId), f.Eq("Answers.$[].Id", ids.AnswerId));
var projection = Builders<BsonDocument>.Projection.Include("Answers.Likes.$");
var likes = await dbClient.GetCollection<BsonDocument>(nameof(Thread))
    .Find(filter)
    .Project(projection)
    .FirstOrDefaultAsync();

But this query always returns null, what I'm doing wrong from this POV?

mongodb json c#
2 Answers

I wasn't able to fetch Likes with single filtering and projection. However, I was able to achieve it by using aggregation pipeline.

private async Task<BsonArray> GetLikes(string docId, string answerId)
{
    var client = new MongoClient();
    var idFilter = Builders<BsonDocument>.Filter.Eq("ID", docId);
    var answerIdFilter = Builders<BsonDocument>.Filter.Eq("Answers.ID", answerId);
    var projection = Builders<BsonDocument>.Projection.Exclude("_id").Include("Answers.Likes");
    var likes = await client.GetDatabase("test").GetCollection<BsonDocument>("items")
        .Aggregate(new AggregateOptions())
        .Match(idFilter)
        .Unwind("Answers")
        .Match(answerIdFilter)
        .Project(projection)
        .FirstOrDefaultAsync();

    return likes == null ? null
        : (likes.GetElement("Answers").Value as BsonDocument).GetElement("Likes").Value as BsonArray;
}

For some reason result included document in original structure as opposed to including just a document with Likes property so I had to do some post processing afterwards.

user_11778140

It is not possible to project the individual fields from array in projection using regular queries.

You can at best project the matching element using regular queries and then map the likes.

Something like

var f = Builders<BsonDocument>.Filter;
var filter = f.And(f.Eq("Id", ids.ThreadId), f.Eq("Answers.Id", ids.AnswerId));
var projection = Builders<BsonDocument>.Projection.Include("Answers.$");
var answer = await dbClient.GetCollection<BsonDocument>(nameof(Thread))
    .Find(filter)
    .Project(projection)
    .FirstOrDefaultAsync();

Alternatively you can use filters with map using aggregation to match the answer element by id followed by projection to map the like field.

Something like

var f = Builders<BsonDocument>.Filter;
var match = f.And(f.Eq("Id", ids.ThreadId), f.Eq("Answers.Id", ids.AnswerId));

var project = new BsonDocument("newRoot", 
            new BsonDocument("$arrayElemAt", new BsonArray {
                new BsonDocument("$map", 
                    new BsonDocument
                        { 
                            { "input", 
                                new BsonDocument("$filter", new BsonDocument
                                    {
                                        { "input", "$Answers"},
                                        {"cond", new BsonDocument("$eq", new BsonArray { "$$this.Id", ids.AnswerId})}
                                    })
                            },
                            { "in", new BsonDocument("Likes", "$$this.UserLikes") }
                        }), 
                0}));    

var pipeline = collection.Aggregate()
.Match(match)
.AppendStage<BsonDocument, BsonDocument, BsonDocument>(new BsonDocument("$replaceRoot", project));

var list = pipeline.ToList();

Working example here - https://mongoplayground.net/p/wM1z6q92_mV

user_26838140
Related