Mongoose Aggregate with Lookup

Question

I have a simple two collections like below :

assignments:

[
    {
        "_id": "593eff62630a1c35781fa325",
        "topic_id": 301,
        "user_id": "59385ef6d2d80c00d9bdef97"
    },
    {
        "_id": "593eff62630a1c35781fa326",
        "topic_id": 301,
        "user_id": "59385ef6d2d80c00d9bdef97"
    }
]

and users collection:

[   
    {
        "_id": "59385ef6d2d80c00d9bdef97",
        "name": "XX"
    },
    {
        "_id": "59385b547e8918009444a3ac",
        "name": "YY"
    }
]

and my intent is, an aggregate query by user_id on assignment collection, and also I would like to include user.name in that group collection. I tried below:

Assignment.aggregate([{
                $match: {
                    "topic_id": "301"
                }
            },
            {
                $group: {
                    _id: "$user_id",
                    count: {
                        $sum: 1
                    }
                }
            },
            {
                $lookup: {
                    "from": "kullanicilar",
                    "localField": "user_id",
                    "foreignField": "_id",
                    "as": "user"
                }
            },
            {
                $project: {
                    "user": "$user",
                    "count": "$count",
                    "_id": "$_id"
                }
            },

But the problem is that user array is always blank.

[ { _id: '59385ef6d2d80c00d9bdef97', count: 1000, user: [] } ]

I want something like :

[ { _id: '59385ef6d2d80c00d9bdef97', count: 1000, user: [_id:"59385ef6d2d80c00d9bdef97",name:"XX"] } ]

Show the actual data as viewed from the mongo shell rather than a `console.log()`. Show your schema for each as well. This is likely that `user_id` in the assignments collection is actually a string. — Neil Lunn, Jun 15 '17 at 08:14
i doubt the same thing actually, user_id is type pf Object Id and look like below in the schema : "_id": { "$oid": "5900a3fe38606b002ba3c59e" }, .... — erkan çipil, Jun 15 '17 at 08:46
From the shell? I don't think so since it's not valid for MongoDB to have `$` in field names. I'm asking about `user_id` in the "assignments" collection. Start the shell and please include the schema in your question like I asked. Then we can tell you for sure. — Neil Lunn, Jun 15 '17 at 08:53
ah the user id in the assignment looks like below : { "_id": { "$oid": "593eff62630a1c35781fa325" }, "project": "input.aiatA2", "user_id": "59385ef6d2d80c00d9bdef97", "topic_id": 301, "is_related": false, "document_id": "FBIS4-22522" } { "_id": { "$oid": "593eff62630a1c35781fa326" }, "project": "input.aiatA2", "user_id": "59385ef6d2d80c00d9bdef97", "topic_id": 301, "is_related": false, "document_id": "FBIS4-62078" } just pasted 2 documents in assignment object — erkan çipil, Jun 15 '17 at 08:59
Yep it's a string. Your schema will also be incorrect, so you should fix both data and schema. Once you do the `$lookup` can match types and will work. — Neil Lunn, Jun 15 '17 at 09:03
Ah you mean , user_id in assignment should be ObjectId type , right? — erkan çipil, Jun 15 '17 at 09:05
Yes. `"59385ef6d2d80c00d9bdef97" != Objectid("59385ef6d2d80c00d9bdef97")`. That is why you get no results. — Neil Lunn, Jun 15 '17 at 09:06
Great thx, that make sense, but i dont know how to create an index which type ObjectType :) anyway, i dont want to bother you anymore, i will try to figure out. Thank you. — erkan çipil, Jun 15 '17 at 09:10
See more: [@Neil Lunn](https://stackoverflow.com/questions/36459983/aggregation-filter-after-lookup) It will help you :) Thanks — Alex, Jun 15 '17 at 09:27

Mongoose Aggregate with Lookup

0 Answers0