$lookup on nested array of Objects

Question

In my project Node and Mongo project, i'm using mongoose for my modelling. I'm trying to perform a lookup on a nested document. I have a thread object, with an array of "post" objects as one of its properties. One of the properties of this nested "post" object, is a user_id, the person who posted. I've tried to lookup the user_id(ie - localfield: thread.post.user_id, from users, foreignfield: _id) but the shell keeps returning nothing.

Can anybody suggest an amendment to what i've tried below:

db.threads.aggregate([
{ "$match": { "posts._id": ObjectId("abcdef") } },
{ "$sort": { "dateAdded": -1 } },
{ "$limit": 15 },
{ "$lookup": {"localField": "posts.user_id","from": "users","foreignField": "_id","as": "userinfo"} },
{ "$unwind": "$userinfo" },
{ "$project":{"dateAdded":1,"userinfo.name":1,"userinfo.username":1}}
]);

And a sample of the records in my collections

db.threads.find({}) returns...

{ 
"_id" : ObjectId("78910"), 
"dateAdded" : ISODate("2017-08-18T16:44:23Z"), 
"title" : "Thread Zero", 
"posts" : [ { 
"_id" : ObjectId("abcdef"), 
"user_id" : ObjectId("12345"), 
"postText" : "good evening", 
"dateAdded" : "2017-8-18 17:44:34" }  ],
 "__v" : 0 
}

db.users.find({}) returns...

Sample user object

{ 
"_id" : ObjectId("12345"), "name" : "James Free", 
"name" : "Al Isonwunderland", 
"password" : "$2a$10$ILpitvg1.o8X8GnaSaoG4ulnuNWrFTUfhQDA8CdihbHPjBrB8NaVm", 
"username" : "muppet", 
"__v" : 0 
}

So from this, I would like to return the name property from the "user" object for each of the posts on the thread. What i've written is an attempt to get the user's name ad username for one specific post, I would assume retrieving the user name and name for all comments is to leave the $match parameter blank, allowing it to return a list of posts along with the user's name/username

Can anyone confirm this?

The field `user_id" : "12345"` contains a "string" which you are trying to match against `_id` which contains an `ObjectId`. Because the two types do not match you get no results. So you need to fix the data in `user_id` to contain the same `ObjectId` values that you are referencing in the other collection. Note also that you need MongoDB 3.4 to use a source in an array. Otherwise you would also need to `$unwind` the array before the `$lookup` using earlier versions. — Neil Lunn, Aug 19 '17 at 01:24
Apologies, typo. user_id in threads.posts should read ObjectId("12345"), not "12345". — Oisín Foley, Aug 19 '17 at 01:57
No it is not `ObjectId("12345")` because that is not a valid value for an `ObjectId`. If you want help with your questions you need to produce the **actual data** and not some abstract or obfuscation. We cannot hack your system based on you posting the actual data here. So just post the actual data in a way that can be reproduced. See [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve). But it's most certainly a type mismatch or an incorrect collection name. Those are the only two possible causes of no result. Or of course the MongoDB version. — Neil Lunn, Aug 19 '17 at 02:02

score 0 · Accepted Answer · answered Aug 19 '17 at 21:08

I swapped out ObjectId for a uuid property as ObjectId is messy to deal with(the Mongo shell's .find() function returns as ObjectId("5998a2a81762e90ce9f55d92"), then when reading from database it's just the alphanumeric("5998a2a81762e90ce9f55d92") returns and then inputting that alphanumeric into the shell to test commands always returns null)

The following solved my problem,

db.threads.aggregate([
    {$match: {uuid: 'de36dd72-238b-47b0-b363-3fbfa1f2743e'}},
    {$unwind:"$posts"},
    {$lookup: {
        from: 'users', 
        localField: 'posts.user_uuid', 
        foreignField: 'uuid', 
        as: 'userInfo'}}
]);

This MongoDB $lookup on nested document proved useful. Hope this helps someone else along the way

$lookup on nested array of Objects

1 Answers1