0

I am creating a mongo aggregation query using a $lookup operator but the $match doesn't work.

db.alarmActive.aggregate(
[  
    {
        $lookup: {
            from: "alarmHistory",
            localField: "history_id",
            foreignField: "_id",
            as: "history"
        }
    },
    { 
        $unwind: { 
            path: "$history", preserveNullAndEmptyArrays: true 
        }
    },
    {
        $lookup: {
            from: "alarmTranslation",
            localField: "history.plcId",
            foreignField: "plcId",
            as: "txt"
        }
    },
    { 
        $unwind: { 
            path: "$txt", preserveNullAndEmptyArrays: true 
        }
    },
    { 
        $unwind: { 
            path: "$txt.text", preserveNullAndEmptyArrays: true 
        }
    },
    {
        $match: {
            $and: [
                { "txt.text.language": "de-DE" },
                { "txt.gc_ref": "$gc_ref" } -> doesn't work
                //{ "txt.gc_ref": 9 }  -> works
            ]
        }
    },
    {
        $lookup: {
            from: "infrastructure",
            localField: "gc_ref",
            foreignField: "Gc_ref",
            as: "gc"
        }
    },
    { 
        $unwind: { 
            path: "$gc", preserveNullAndEmptyArrays: true 
        }
    },
    {
        $group: {
            _id: { 
                gc_ref: "$gc_ref", 
                name: "$gc.Name"
            },
            alarms: {
                $push: {
                    gendate: "$history.gendate",
                    type: "$txt.type",
                    location: "$history.location",
                    text: "$txt.text.text"
                }
            }
        }
    }
]).pretty();
Dennis Heindl
  • 1
  • Because you are trying to compare to a field of the document itself `{ "txt.gc_ref": "$gc_ref" }` and `$match` does not do that. You use [`$redact`](https://docs.mongodb.com/v3.4/reference/operator/aggregation/redact/) for that type of comparison instead. – Neil Lunn Aug 01 '17 at 11:07
  • Thank you, it worked. `{ $match: { "txt.text.language": "de-DE" } }, { $redact: { $cond: { if: { $eq: [ "$txt.gc_ref", "$gc_ref" ] }, then: "$$KEEP", else: "$$PRUNE" } } }` – Dennis Heindl Aug 01 '17 at 11:25

0 Answers0