Mgo how to find nested document inside nested arrays?

Here below I have document structure designed as follows:

type MIS_Course struct {
	ID   bson.ObjectId `bson:"_id,omitempty"`
	Name string        `bson:"crse_name"`
}

type MIS_Department struct {
	ID      bson.ObjectId `bson:"_id,omitempty"`
	Name    string        `bson:"dept_name"`
	Courses []MIS_Course  `bson:"crse_list"`
}

type MIS_School struct {
	ID          bson.ObjectId    `bson:"_id,omitempty"`
	Name        string           `bson:"school_name"`
	Departments []MIS_Department `bson:"dept_list"`
}

And when initialized, I would have a "School ABC" in a sea of schools containing something that looks like below:

{
	"_id" : ObjectId("55fbb522022aae7b3a000003"),
	"school_name" : "School ABC",
	"dept_list" : [
		{
			"dept_name" : "Department A",
			"crse_list" : [
				{
					"crse_name" : "Class A"
				},
                {
					"crse_name" : "Class B"
				},
			]
		}
	]
}

For hours on end I couldn't find a solution that effectively works by given the school_name, dept_name, and crse_name:

Find the dept_list of school_name > find the crse_list of dept_name > find crse_name

The reason that such chain of finds are required is because the scope of the find should be limited to the school, and then the department. Logic and housekeeping happens after the each stages of finds.

I tried code such as

result := MIS_School{}

err := database.C("schools").Find(bson.M{"school_name": school}).Select(
bson.M{"dept_list": bson.M{"$elemMatch": bson.M{"dept_name": dept}}}).Select(
bson.M{"crse_list": bson.M{"$elemMatch": bson.M{"crse_name": crse}}}).One(&result)

But it didn't work since Select projections can't be chained in Mgo(?)

I have read from somewhere that mongodb doesn't have the capability to directly retrieve documents nested in arrays deeper than 1-2 levels (Getting Document C inside B array inside A array). Is that true? How could I work around this problem?

Thanks!

You can chain the Select statements, but the value of the second call will override the value of the first call, rather than doing what the example implies it does, which is to dive deeper into the nested structure.

To achieve what apparently you are trying to do, you may use the aggregation framework to manipulate these nested objects in arbitrary ways.

For example, this is a simple pipeline that would pull out the exact matching courses:

    pipeline := []bson.M{
            {"$match": bson.M{"school_name": school}},
            {"$unwind": "$dept_list"},
            {"$unwind": "$dept_list.crse_list"},
            {"$match": bson.M{
                     "dept_list.dept_name": dept,
                     "dept_list.crse_list.crse_name": crse,
            }},
    }
    iter := coll.Pipe(pipeline).Iter()

You can use the resulting iterator the same way you use iterators from Find.

For this pipeline, the resulting objects will look like:

    bson.M{
            "_id":"...",
            "dept_list": bson.M{
                    "dept_name": "Department A",   
                    "crse_list": bson.M{
                            "crse_name": "Class B",
                    }
            },
            "school_name":"School ABC",
    }

You can change the shape of the resulting object in arbitrary ways, though. Have a look at the aggregation framework documentation for more details.

After reading the mongo docs I think the preferred way to do nested relationship is to use tree structures with materialized paths as described here on mongo.

Also available are trees of array of ancestors and parent / child references. Take a look.

It seems using trees is simpler, more elegant, and saves a lot of technical hassle.