CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
LienM
LienM

Reputation: 145

MongoDB select documents where field1 equals nested.field2 in aggregate pipeline

I have joined two collections on one field using '$lookup', while actually I needed two fields to have a unique match. My next step would be to unwind the array containing different values of the second field I need for a unique match and then compare these to the value of the second field it needs to match higher up. However, the second line in the snippet below returns no results. 

// Request only the page that has been viewed
{ '$unwind' : '$DSpub.PublicationPages'},
{ '$match' : {'pageId' :  '$DSpub.PublicationPages.PublicationPageId' } }

Is there a more appropriate way to do this? Or can I avoid doing this altogether by unwinding the "from" collection before performing the '$lookup', and then match both fields?

Upvotes: 1

Views: 1144

Answers (1)

profesor79
profesor79

Reputation: 9473

This is not as easy at it looks.

$match does not operate on dynamic data (that means we are comparing static value against data set). To overcome that - we can use $project phase to add a bool static flag, that can be utilized by $match

Please see example below:

Having input collection like this:

[{
        "_id" : ObjectId("56be1b51a0f4c8591f37f62b"),
        "name" : "Alice",
        "sub_users" : [{
                "_id" : ObjectId("56be1b51a0f4c8591f37f62a")
            }
        ]
    }, {
        "_id" : ObjectId("56be1b51a0f4c8591f37f62a"),
        "name" : "Bob",
        "sub_users" : [{
                "_id" : ObjectId("56be1b51a0f4c8591f37f62a")
            }
        ]
    }
]

We want to get only fields where _id and $docs.sub_users._id" are same, where docs are $lookup output.

db.collecction.aggregate([{
            $lookup : {
                from : "collecction",
                localField : "_id",
                foreignField : "_id",
                as : "docs"
            }
        }, {
            $unwind : "$docs"
        }, {
            $unwind : "$docs.sub_users"
        }, {
            $project : {
                _id : 0,
                fields : "$$ROOT",
                matched : {
                    $eq : ["$_id", "$docs.sub_users._id"]
                }
            }
        }, {
            $match : {
                matched : true
            }
        }
    ])

that gives output:

{
    "fields" : {
        "_id" : ObjectId("56be1b51a0f4c8591f37f62a"),
        "name" : "Bob",
        "sub_users" : [ 
            {
                "_id" : ObjectId("56be1b51a0f4c8591f37f62a")
            }
        ],
        "docs" : {
            "_id" : ObjectId("56be1b51a0f4c8591f37f62a"),
            "name" : "Bob",
            "sub_users" : {
                "_id" : ObjectId("56be1b51a0f4c8591f37f62a")
            }
        }
    },
    "matched" : true
}

Upvotes: 2

Related Questions