Geek Answers Handbook

MongoDB $ search within a subdocument

I am new to mongo and struggle very much with the following. I have two collections structured as shown below. In my life, I cannot figure out how to do a search in the school collection. While reading other posts, I definitely use ObjectId for reference, as well as for an external field.

Below is my structure:

Alumni:

{ "_id": "john", "items": [ { "name": "John", "items": [ { "school": ObjectId("56de35ab520fc05b2fa3d5e4"), "grad": true }, { "school": ObjectId("56de35ab520fc05b2fa00000"), "grad": false } ] }, { "name": "Johnny" // notice no nested items, this doc should still be included in result }, { "name": "Jon", "items": [ { "school": ObjectId("56de35ab520fc05b2fa11111"), "grad": false } ] } ] }

Schools

 { _id: ObjectId("56de35ab520fc05b2fa3d5e4"), name: "Some University", street: "ABC Boulevard" }

What I want to get:

 { "_id": "john", "items": [ { "name": "John", "items": [ { "school": ObjectId("56de35ab520fc05b2fa3d5e4"), "grad": true, "schoolInfo": { _id: ObjectId("56de35ab520fc05b2fa3d5e4"), name: "Some University", street: "ABC Boulevard" } }, { "school": ObjectId("56de35ab520fc05b2fa00000"), "grad": true, "schoolInfo": { _id: ObjectId("56de35ab520fc05b2fa00000"), name: "Another University", street: "123 Boulevard" } } ] }, { name: "Johnny" }, { "name": "Jon", "items": [ { "school": ObjectId("56de35ab520fc05b2fa11111"), "grad": true, "schoolInfo": { _id: ObjectId("56de35ab520fc05b2fa11111"), name: "Some University", street: "ABC Boulevard" } } ] } ] }

The query I tried to no avail:

 db.alumni.aggregate([ {$match: {_id: 'john'}}, {$lookup: { from: 'schools', localField: 'items.items.school', foreignField: '_id', as: 'schoolInfo'}} ])

Any help would be greatly appreciated!

+7
mongodb mongodb-query
source share
2 answers

in this case, you need a good game with $ unwind and $ project in the aggregation infrastructure

see below:

 db.alumni.aggregate([ {$match: {_id: 'john'}}, {$unwind:"$items"}, {$unwind:"$items.items"}, {$lookup: { from: 'schools', localField: 'items.items.school', foreignField: '_id', as: 'schoolInfo'}}, {$unwind:"$schoolInfo"}, {$project:{ "_id":1, "items":[{ "name":"$items.name", "items":[{ "school":"$schoolInfo._id" , "grad":"$items.items.grad" , "schoolInfo":"$schoolInfo" }] }] }} ]).pretty()

To see how this works, try removing the aggregation steps from the query and checking the structure of the document.

+14
source share

Would it be better to handle multiple schools in an $items.items ?

 db.alumni.aggregate([ {$match: {_id: 'john'}}, {$unwind:"$items"}, {$unwind:"$items.items"}, {$lookup: { from: 'schools', localField: 'items.items.school', foreignField: '_id', as: 'schoolInfo'}}, {$unwind:"$schoolInfo"}, {$group:{ _id: { _id: '$_id', name: '$items.name', }, items: { $push: { 'grad': '$items.items.grad', 'school': '$schoolInfo._id' 'schoolInfo': '$schoolInfo' } } }}, {$group:{ _id: '$_id._id', items: { $push: { 'name': '$_id.name', 'items': '$items' } } }} ]).pretty()

I did not consider the case of the absence of $items.items , but you can see $ unwind an empty array. In addition, it would be better to leave an empty array instead of nothing when there is no entry, i.e.

 { name: "Johnny", items: [], },
+1
source share

More articles:


All Articles