How to map array of objects to array of object properties with nested array MongoDB c#

Question

I'm trying to create the following methods to retrieve an array of ObjectId from a child array.

GetStorageLocationIds()

GetStorageRoomIds(ObjectId locationId)

GetStorageSectionIds(ObjectId locationId, ObjectId roomId)

GetStorageShelfIds(ObjectId locationId, ObjectId roomId, ObjectId sectionId)

GetStorageSlotIds(ObjectId locationId, ObjectId roomId, ObjectId sectionId, ObjectId shelfId) (this one may not be as difficult because it is already an array of ObjectId)

Each method should return an IEnumerable<ObjectId> containing an array of the id property of the appropriate array. I realize I could just get the entire document for every list but I would prefer a more efficient and elegant solution using the MongoDB c# driver.

This is an example document:

{
"_id" : ObjectId("5cb2271a4bd93c0dec4db163"),
...
"StorageRooms" : [
    {
        "_id" : ObjectId("5cb49adc36ad6719bf947103"),
        ...
        "StorageSections" : [ ]
    },
    {
        "_id" : ObjectId("5cb49afa36ad6719bf947106"),
        ...
        "StorageSections" : [
            {
                "_id" : ObjectId("5cb49bb8c40cd319cb2511ae"),
                ...
                "StorageShelves" : [ ]
            },
            {
                "_id" : ObjectId("5cb49bb8c40cd319cb2511af"),
                ...
                "StorageShelves" : [
                    {
                        "_id" : ObjectId("5cb49bfe8d259019d9207f48"),
                        ...
                        "StorageSlotIds" : [ ]
                    },
                    {
                        "_id" : ObjectId("5cb49bfe8d259019d9207f49"),
                        ...
                        "StorageSlotIds" : [ ]
                    }
                ]
            }
        ]
    },
    {
        "_id" : ObjectId("5cb49afa36ad6719bf947107"),
        ...
        "StorageSections" : [ ]
    }
]

}

The above methods should return the following array, using the above example document as the input data. (assuming it is the only one in the collection):

GetStorageLocationIds() -> ["5cb2271a4bd93c0dec4db163"]

GetStorageRoomIds("5cb2271a4bd93c0dec4db163") -> ["5cb49adc36ad6719bf947103,"5cb49afa36ad6719bf947106", "5cb49afa36ad6719bf947107"]

GetStorageSectionIds("5cb49afa36ad6719bf947106") -> ["5cb49bb8c40cd319cb2511ae","5cb49bb8c40cd319cb2511af"]

etc...

So far, I've been able to write the first one: GetStorageLocationIds. This is the code seems to work well:

public async Task<IEnumerable<ObjectId>> GetAllDocumentIdsAsync(string database, string collection,
        CancellationToken cancellationToken)
    {
        return (await _mongoContext.MongoClient.GetDatabase(database).GetCollection<T>(collection)
            .Find(new BsonDocument())
            .Project(new BsonDocument {{"_id", 1}})
            .ToListAsync(cancellationToken)).Select(x => x[0].AsObjectId);
    }

When it comes to the next one, I tried using ProjectionDefinition but all it did was return the document id instead of each id in the StorageRooms array.

public async Task<IEnumerable<ObjectId>> GetStorageRoomIdsAsync(ObjectId id, CancellationToken cancellationToken)
    {
        var filter = Builders<StorageLocation>.Filter.And(
            Builders<StorageLocation>.Filter.Where(location => location.Id == id));
        var projectionDefinition = Builders<StorageLocation>.Projection.Include(location => location.StorageRooms);

        var projectionResult = await ProjectAsync(filter, projectionDefinition, cancellationToken);
        return projectionResult.Select(x => x[0].AsObjectId);
    }

After some attempts at using aggregation, I believe it will work with Unwind but I'm lost at how to implement this in c# correctly. Thanks in advance for any help.

Edit Note: ObjectId and string are used exchangeably in this question for brevity. I use AutoMapper for my actual project

Edit 2:

The answer from micki worked for GetStorageRoomIds. I'm now trying to use the following code for GetStorageSectionIds but get an error:

return from location in AsQueryable()
            where location.Id == id
            from room in location.StorageRooms
            where room.Id == roomId
            from section in room.StorageSections 
            select section.Id;

The error is here

Answer 1

For queries like this you can run AsQueryable() on your IMongoCollection and then use LINQ syntax like below:

var storageRoomIds = from location in Col.AsQueryable()
                     where location.Id == locationId
                     from room in location.StorageRooms
                     select room.Id;

You can also turn on MongoDB profiler to see that it will be translated into

"pipeline" : [
    {
        "$match" : {
            "_id" : ObjectId("5cb2271a4bd93c0dec4db163")
        }
    },
    {
        "$unwind" : "$StorageRooms"
    },
    {
        "$project" : {
            "_id" : "$StorageRooms._id"
        }
    }
],

So you have both simple C# code and efficient MongoDB query

EDIT: for level below you can still use LINQ syntax:

var rooms = from location in Col.AsQueryable()
            where location.Id == locationId
            from room in location.StorageRooms
            select new
            {
                roomId = room.Id,
                storageIds = room.StorageSections.Select(x => x.Id)
            };

var storageIds = from room in rooms
                where room.roomId == roomId
                from storageId in room.storageIds
                select storageId;


var result = storageIds.ToList();

and this gets translated into:

"pipeline" : [
    {
        "$match" : {
            "_id" : ObjectId("5cb2271a4bd93c0dec4db163")
        }
    },
    {
        "$unwind" : "$StorageRooms"
    },
    {
        "$project" : {
            "roomId" : "$StorageRooms._id",
            "storageIds" : "$StorageRooms.StorageSections._id",
            "_id" : 0
        }
    },
    {
        "$match" : {
            "roomId" : ObjectId("5cb49afa36ad6719bf947106")
        }
    },
    {
        "$unwind" : "$storageIds"
    },
    {
        "$project" : {
            "storageIds" : "$storageIds",
            "_id" : 0
        }
    }
],