MongoDB lookup when foreign field is an array

Question

I've searched the internet and StackOverflow, but I cannot find the answer or even the question.

I have two collections, reports and users. I want my query to return all reports and indicate if the specified user has that report as a favorite in their array.

Reports Collection

{ _id: 1, name:"Report One"}
{ _id: 2, name:"Report Two"}
{ _id: 3, name:"Report Three"}

Users Collection

{_id: 1, name:"Mike", favorites: [1,3]}
{_id: 2, name:"Tim", favorites: [2,3]}

Desired Result for users.name="Mike"

{ _id: 1, name:"Report One", favorite: true}
{ _id: 2, name:"Report Two", favorite: false}
{ _id: 3, name:"Report Three", favorite: true}

All of the answers I can find use $unwind on the local (reports) field, but in this case the local field isn't an array. The foreign field is the array.

How can I unwind the foreign field? Is there a better way to do this?

I saw online that someone suggested making another collection favorites that would contain:

{ _id: 1, userId: 1, reportId: 1 }
{ _id: 2, userId: 1, reportId: 3 }
{ _id: 3, userId: 2, reportId: 2 }
{ _id: 4, userId: 2, reportId: 3 }

This method seems like it should be unnessesary. It should be simple to join onto an ID in a foreign array, right?

score 8 · Accepted Answer · edited May 14 '21 at 08:58

You can use $lookup with custom pipeline which will give you 0 or 1 result and then use $size to convert an array to single boolean value:

db.reports.aggregate([
    {
        $lookup: {
            from: "users",
            let: { report_id: "$_id" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                { $eq: [ "$name", "Mike" ] },
                                { $in: [ "$$report_id", "$favorites" ] }
                            ]
                        }
                    }
                }
            ],
            as: "users"
        }
    },
    {
        $project: {
            _id: 1,
            name: 1,
            favorite: { $eq: [ { $size: "$users" }, 1 ] }
        }
    }
])

Alternatively if you need to use MongoDB version lower than 3.6 you can use regular $lookup and then use $filter to get only those users where name is Mike:

db.reports.aggregate([
    {
        $lookup: {
            from: "users",
            localField: "_id",
            foreignField: "favorites",
            as: "users"
        }
    },
    {
        $project: {
            _id: 1,
            name: 1,
            favorite: { $eq: [ { $size: { $filter: { input: "$users", as: "u", cond: { $eq: [ "$$u.name", "Mike" ] } } } }, 1 ] }
        }
    }
])

I should have mentioned I'm using MongoDB 4. This makes sense. Do I have to do something different if all of those _id's are ObjectId's? — Michael Cox, Nov 13 '18 at 18:48
@mickl what if the foreignField is an array of objects, not just strings? in this case, what if favorites contained both the `name` and `id`? — Abe Caymo, Nov 05 '21 at 07:48

B.K tech_Info · Answer 2 · 2022-03-15T04:16:28.627

    "_id" : ObjectId("611fc392cfadfbba65d4f4bd"),
    "t_name" : "Bahadur",
    "t_age" : "22",
    "trch" : "java",
    "StudentsDetails" : [
        {
            "_id" : ObjectId("611fc41ccfadfbba65d4f4be"),
            "s_name" : "Asin",
            "s_age" : "18",
            "trch" : "java",
            "tsid" : ObjectId("611fc392cfadfbba65d4f4bd")
        },
        {
            "_id" : ObjectId("611fc8f1a815fb2c737ae31f"),
            "s_name" : "sonu",
            "s_age" : "18",
            "tsid" : ObjectId("611fc392cfadfbba65d4f4bd")
        },
        {
            "_id" : ObjectId("611fc915a815fb2c737ae320"),
            "s_name" : "monu",
            "s_age" : "19",
            "tsid" : ObjectId("611fc392cfadfbba65d4f4bd")
        }
    ]
}

Create Trainer Collection Create Scholar Collection

//query

db.Trainer.aggregate(
    [`enter code here`
        {`enter code here`
            $lookup:`enter code here`
            {`enter code here`
                from: "scholar",`enter code here`
                localField: "_id",`enter code here`
                foreignField: "tsid",`enter code here`
                as: "StudentsDetails"`enter code here`
            }`enter code here`
        }`enter code here`
    ]`enter code here`
).pretty();

MongoDB lookup when foreign field is an array

2 Answers2

Linked