How to combine where clause and join?

Question

If I were to write this in SQL it would be (sort of):

SELECT *
FROM request req, response res
WHERE req.suites_test=res.key    # join
  AND res.version='2.0.b1662.5'  # extra conditions
  AND req.suites_id='58762c40664df86d2069e2c9'

In MongoDB I can do:

# a join between request and response 
db.response.aggregate([{$lookup: {from: "request", localField: "key", foreignField: "suites.test", as: "matching"} } ])

# find all requests that match a condition
db.request.find( { "suites.id": ObjectId("58762c40664df86d2069e2c9") } )

# find all responses that match a condition
db.response.find( { "version": "2.0.b1662.5" } )

How can I combine the three in a single MongoDB query?

I am new to MongoDB don't know what's modern explicit JOIN syntax ... in the documentation https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/ it doesn't say it is deprecated. — SkyWalker, Jan 17 '17 at 13:50
[Aggregate](https://docs.mongodb.com/manual/aggregation/) is actually a pipeline, match your documents there. — Alex Blex, Jan 17 '17 at 13:51
Based on your individual queries, you will just need to include the `$match` stage for the `response` ( before `$lookup`) and `request ` ( after `$unwind` which follows `$lookup` ) collection in your aggregation pipeline. — s7vr, Jan 17 '17 at 14:11
@Veeram thank you yes I managed with the response bit ... any chance you can make an answer? — SkyWalker, Jan 17 '17 at 14:18

score 1 · Accepted Answer · answered Jan 17 '17 at 14:53

1

There are a bunch of duplicates which are similar, but I can't find the exact matching duplicate. So, I'm adding an answer based on OP's request.

Based on your individual queries, you will just need to include the $match stage for the response ( before $lookup) and request ( after $unwind which follows $lookup ) collection in your aggregation pipeline. Something like below.

db.response.aggregate([{
    $match: {
        "version": "2.0.b1662.5"
    }
}, {
    $lookup: {
        from: "request",
        localField: "key",
        foreignField: "suites.test",
        as: "matching"
    }
}, {
    $unwind: "$matching"
}, {
    $match: {
        "matching.id": ObjectId("58762c40664df86d2069e2c9")
    }
}])

answered Jan 17 '17 at 14:53

s7vr

73,656
11
106
127

1

It is because you can't find an exact duplicate that we call it *possible duplicates* – styvane Jan 17 '17 at 14:55
1

At the end you just pollute the SO with these possible duplicates and is no wonder that I find questions that are marked as possible duplicates of other questions that have nothing to do with what I was originally looking for, thus polluting SO with ping-pong possible duplicates. – SkyWalker Jan 17 '17 at 14:58
Understood, but I felt possible duplicates are not closer to OP's question but its purely one's perspective. – s7vr Jan 17 '17 at 14:58
1

Now anyone looking for a simple way to mix join and where clauses will find it in this in this Q/A instead of bouncing around in SO hopelessly. – SkyWalker Jan 17 '17 at 14:59

How to combine where clause and join?

1 Answers1