CODEWITHSUNDEEP
mongodb
shuba.ivan
shuba.ivan

Reputation: 4051

Mongodb $lookup with condition

I have mongodb version 4.4.3 and I want to fetch all allerts which has tmeplates relation and which templates has deviceStatus relation

Alert document

{
    "_id" : ObjectId("6034f08a025fd735035d26c4"),
    "templates" : [ 
        {
            "$ref" : "Template",
            "$id" : ObjectId("6034f050bba852116468c993")
        }
    ]
}

and Templat document

{
    "_id" : ObjectId("6034f050bba852116468c993"),
    "deviceStatus" : {
        "value" : "hold"
    },
}

since like that link my query should return me object alert where should be full object Template

db.getCollection('Alert').aggregate([  
    {
        $lookup: {
            from: 'Template',
            localField: 'templates',
            foreignField: '_id',
            as: 'templateObject'
        }
    }
])

but I had empty templateObject Or this happened becuase I have templates array objects which contains two column $ref and $id instead exactly $id for correct lookup ? How to buid this query ritgh ?

When I changed structure to

"templates" : [ 
        ObjectId("6041e12c2845427b5f2ff7d3")
    ],

instead fo

"templates" : [ 
    {
        "$ref" : "Template",
        "$id" : ObjectId("6034f050bba852116468c993")
    }
]

everything works correct

My question how to reproduce the same way when I have this struture ?

    "templates" : [ 
        {
            "$ref" : "Template",
            "$id" : ObjectId("6034f050bba852116468c993")
        }
    ]

Upvotes: 0

Views: 72

Answers (2)

Fraction
Fraction

Reputation: 12954

Just use 'templates.$id' in localField:

db.getCollection('Alert').aggregate([  
    {
        $lookup: {
            from: 'Template',
            localField: 'templates.$id',
            foreignField: '_id',
            as: 'templateObject'
        }
    }
])

Upvotes: 0

Dheemanth Bhat
Dheemanth Bhat

Reputation: 4452

Try this:

db.getCollection('Alert').aggregate([
    {
        $lookup: {
            from: 'Template',
            let: { "template_id": "$templates.$id" },
            pipeline: [
                {
                    $match: {
                        $expr: { $in: ["$_id", "$$template_id"] }
                    }
                }
            ],
            as: 'templateObject'
        }
    }
]);

Upvotes: 1

Related Questions