Handling nullish values in pipeline expression

Working with Data
1

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.

2

Hello : )

You can filter out the null values before doing the join(but you will lose some t5 documents).
If you want to always keep t5 documents,you can do it in the pipeline of t6(in the lookup).

In the outer pipeline (fast if index)

{"$match" : {"age" : {"$ne" : null}}}

Or in the inner pipeline (fast if index only in mongodb 5)

{"$match"
      {"$expr"
       {"$and" [{"$eq" ["$$p_age" "$old"]} {"$ne" ["$old" nil]}]}}}

But to use index in the in the inner pipeline with $expr you need MongoDB 5.
(you have to use $expr because you use equality with variables)
It will work even if not mongodb 5 but will not use the index.

You can unwind after etc ,the false on preserve… is the default so you dont need the option.

3

Thanks @Takis for response. Can you please provide more details on this?

As I asked previously, this is expected behaviour in case of null value handling in MongoDB? If yes then please point me to any documentation/blog where all these details related to null value handling are mentioned on MongoDB’s official source of information(If possible with example).

{“$match”
{“$expr”
{“$and” [{“$eq” [“$$p_age” “$old”]} {“$ne” [“$old” nil]}]}}}

Can we write above query in the following way:

{"$match"
      {"$expr"
       {"$and" [{"$eq" ["$$p_age" "$old"]} {"$ne" ["$old" null]}]}}}

i.e. using “null” instead of “nil”?

This behaviour of null value handling is specific to any version or this is uniform across all supported version of MongoDB?

Going forward, this behaviour is going to change in near future in which “null” won’t be treated as “zero”.

I personally feel this is the bug and should be corrected. There shouldn’t be comparison between null values.

You can refer (https://jira.mongodb.org/browse/SERVER-6471). Similarly, we don’t need to consider null value columns in the expression of pipeline.

Let me know where can I raise this concern and file the Jira.

Thanks again.

4

MongoDB joins the null values.
If a field is missing its supposed to have null value also.

See this example
$lookup example from documentation

nil is typo,its the null in Clojure that i use , i meant null

I don’t know if they consider it a bug,or if they are planning to change it.
I never created a jira i dont know if its possible for all to create one,maybe someone else can help i would like to know also.

5

Thanks @Takis for detailed information. The provided example really helps.

I don’t know if they consider it a bug,or if they are planning to change it.

Can you please escalate or confirm from dev team regarding the same?

I am awaiting for reply from dev team member for more clarification.