CODEWITHSUNDEEP
mongodbjoin
Phoenix Dev
Phoenix Dev

Reputation: 457

Mongodb match on a lookup table not working

How can I match a condition on a column that belongs to a joined table?

For eg:
Consider two collections A and B:
Collection A: 
id|name|status
++++++++++++++
1 |Rock|1
2 |Sam |1
3 |Jack|1
Collection B: 
id|userId|      loc       | status
+++++++++++++++++++++++++++++++++++
1| 1     |[11.111,22.321] | -1
2| 1     |[16.22,33.213]  |  1
3| 2     |[334.11,242.321]|  1
4| 3     |[1.111,224.321] |  1

I want to get all users with their current location based on status field in collection B, that is user's current location will have status 1 in B and previous location have status -1. 

So, the result I want is like:
+++++++++++++++++
id:1, name: Rock, status: 1, userLocTable: [{id:2, userId:1, loc: [16.22,33.213], status: 1}]

What I am doing is:

db.collectionA.aggregate([
    {
        $lookup: {
            from: collectionB,
            localField: id,
            foreignField: userId,
            as: userLocTable
        }
    },
    {
        $match:{
           userLocTable.status: 1
        }
    }
])

But I am getting all rows(both status 1 and -1) from the lookup table (B) instead of only records with status 1. What could be the problem. Any help wuld be appreciated. Thanks!

Upvotes: 0

Views: 357

Answers (1)

Pavan Vora
Pavan Vora

Reputation: 1744

Try this one,

db.collectionA.aggregate([
    { 
        "$lookup": { 
            "from": collectionB,
            "localField": id,
            "foreignField": userId,
            "as": userLocTable
        } 
    },
    {
        "$addFields": {
            "userLocTable": {
                        "$filter": {
                            "input": "$userLocTable",
                            "as": "userLoc",
                            "cond": {
                                "$eq": [ "$$userLoc.status", "1" ]
                            }
                        }
            }
        }
    }
])

Upvotes: 3

Related Questions