CODEWITHSUNDEEP
c#mongodbmongodb-.net-driver
BigBawss
BigBawss

Reputation: 85

Query a deeply nested object in arrays using MongoDB and C#

I'm pretty new to MongoDB and already I face a sweaty challenge. I'm trying to get an element from an array with objects inside of an array with objects (if that makes sense). This is how a document looks like and what I am trying to get:

https://i.imgur.com/W4Wfmhc.png

So basically the structure is as follows:

To get the OptionMenuItem I matched to pull it off using the aggregation pipeline tool from MongoDB Compass tool 

[{
    $unwind: {
        path: '$subjects'
    }
}, {
    $unwind: {
        path: '$subjects.items'
    }
}, {
    $project: {
        _id: '$subjects.items._id',
        item: '$subjects.items'
    }
}, {
    $match: {
        _id: ObjectId('5e6eaef8ae35a418f4f6dbd4')
    }
}]

Then I tried translating this to C# with no success, this is as far as I've come:

        var optionMenuItem = await collection.Aggregate()
           .Unwind<OptionMenu, OptionMenuSubject>(i => i.Subjects)
           .Unwind<OptionMenuSubject, OptionMenuItem>(i => i.Items)
           .Match(i => i.Id == id)
           .ToListAsync();

If somebody knows what I'm doing wrong or how I can pull this off it would be very much appreciated :)

Upvotes: 2

Views: 1659

Answers (2)

BigBawss
BigBawss

Reputation: 85

This is how I eventually solved it. Not my proudest work because it doesn't have the strong typing in it, but it works:

var collection = Database.GetCollection<BsonDocument>(_collectionName);    
var query = await collection.Aggregate()
                .Match(i => i["subjects.items._id"] == ObjectId.Parse(id))
                .Unwind(i => i["subjects"])
                .Unwind(i => i["subjects.items"])
                .Match(i => i["subjects.items._id"] == ObjectId.Parse(id))
                .ReplaceRoot(i => i["subjects.items"])
                .FirstOrDefaultAsync();

Upvotes: 1

Dĵ ΝιΓΞΗΛψΚ
Dĵ ΝιΓΞΗΛψΚ

Reputation: 5669

unwinding unfiltered data is not a good idea because it creates a lot of data in mongodb memory and you might reach the 100mb aggregation pipeline limit. so try to always filter and narrow down records before unwinding. i believe the following pipeline would give the result you need:

db.collection.aggregate([
    {
        $match: {
            'subjects.items._id': ObjectId('5e6eaef8ae35a418f4f6dbd4')
        }
    },
    {
        $unwind: '$subjects'
    },
    {
        $unwind: '$subjects.items'
    },
    {
        $match: {
            'subjects.items._id': ObjectId('5e6eaef8ae35a418f4f6dbd4')
        }
    },
    {
        $replaceWith: '$subjects.items'
    }
])

here's a convenient way to execute this pipeline with c#. you can read more about how it works here.

Upvotes: 0

Related Questions