Is there an equivalent to LEFT JOIN query where right collection isn't exists in MongoDB?
SQL:
SELECT * FROM TableA as A LEFT JOIN TableB as B ON A.id = B.id
WHERE B.Id IS NULL
MongoDB: ???
P.S.: My initial sketch:
db.getCollection('collA').aggregate([
{
$lookup:
{
from: "collB",
localField: "_id",
foreignField: "_id",
as: "collB"
}
}
//, {$match : collB is empty}
])
MongoDB Joins are performed by Lookup. It performs a Left Outer Join to two or more Collections. But Lookup is only permitted in Aggregate operations. This is like a pipeline that performs query, filter and group operations.
For performing MongoDB Join two collections, you must use the $lookup operator. It is defined as a stage that executes a left outer join with another collection and aids in filtering data from joined documents. For example, if a user requires all grades from all students, then the below query can be written: Students.
Much like you would use a join to combine information from different tables in a relational database, MongoDB has a $lookup operation that allows you to join information from more than one collection.
sessions collection. Performs a left outer join to another collection in the same database to filter in documents from the "joined" collection for processing. Filters the document stream to allow only matching documents to pass unmodified into the next pipeline stage.
Well your edit basically has the answer. Simply $match
where the array is empty:
db.getCollection('collA').aggregate([
{ "$lookup": {
"from": "collB",
"localField": "_id",
"foreignField": "_id",
"as": "collB"
}},
{ "$match": { "collB.0": { "$exists": false } } }
])
The $exists
test on the array index of 0
is the most efficient way to ask in a query "is this an array with items in it".
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With