If we compare two null values then that resulting into TRUE. Example:
We have two collections, t5 and t6:
db.t5.find()
{ “_id” : ObjectId(“60dec4f1b968767a8445e1d2”), “id” : 1, “age” : 24, “name” : “dvd” }
{ “_id” : ObjectId(“60dec531b968767a8445e1d5”), “id” : 1, “age” : null, “name” : null }
db.t6.find()
{ “_id” : ObjectId(“60dec507b968767a8445e1d3”), “no” : 2, “old” : 25, “alias” : “vdd” }
{ “_id” : ObjectId(“60dec513b968767a8445e1d4”), “no” : 1, “old” : 24, “alias” : “dvd” }
{ “_id” : ObjectId(“60dec58db968767a8445e1d6”), “no” : 3, “old” : null, “alias” : null }
Join on age and old fields of collections t5 and t6 resp.
db.t5.aggregate([ {$lookup: {
from: "t6",
let : { v_age : "$age", v_id : "$id" },
pipeline: [ { $match:{ $expr:
{"$eq" : [ "$$v_age", "$old" ]}
}
}
],
as: "joined_result"
}},
{$unwind: {path: "$joined_result", preserveNullAndEmptyArrays: false}}
])
This results into:
{ “_id” : ObjectId(“60dec4f1b968767a8445e1d2”), “id” : 1, “age” : 24, “name” : “dvd”, “joined_result” : { “_id” : ObjectId(“60dec513b968767a8445e1d4”), “no” : 1, “old” : 24, “alias” : “dvd” } }
{ “_id” : ObjectId(“60dec531b968767a8445e1d5”), “id” : 1, “age” : null, “name” : null, “joined_result” : { “_id” : ObjectId(“60dec58db968767a8445e1d6”), “no” : 3, “old” : null, “alias” : null } }
From the second row of result, we can say that “null = null” is TRUE.
But result is in Postgres, it will only result 1st row.
Please confirm that this is expected? If yes then how to achieve the result same as Postgres which result into FALSE when we compare two NULL values.
Let me know, if there is stage/operator to achieve SQL like behaviour in MongoDB.