[SERVER-18759] Add $isNull aggregation expression Created: 31/May/15  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.0.3
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Matt Kalan Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: expression, pm1457-nominee
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-13903 inconsistent treatment of null in $pr... Backlog
Assigned Teams:
Query Optimization
Participants:

 Description   

We should add an $isNull aggregation expression, that returns true for missing, null, or undefined values.

Original Description

I have documents that sometimes have an array field called purchases and sometimes not and it fails when $size hits a document without the purchases field. I don't see how to check if it is an array before running it. $exists does not seem to work within $project nor $type to check if it is an array type. The easiest would be if $size just doesn't fail and returns null.



 Comments   
Comment by Asya Kamsky [ 09/Feb/18 ]

Never mind my previous comment, I forgot about false and 0 which are not considered "null-ish" but would return "true" here.

Comment by Asya Kamsky [ 09/Feb/18 ]

Is there a need for $isNull expression given that it's exactly the same as $not of the same value?

{$isNull:"$exp"} is true guarantees that {$not:"$exp"} is also true.

db.badtypes.aggregate({$project:{_id:0, isNull_A:{$not:"$a"}, type_A:{$type:"$a"}, original_A:"$a"}})
{ "isNull_A" : true, "type_A" : "null", "original_A" : null }
{ "isNull_A" : true, "type_A" : "missing" }
{ "isNull_A" : true, "type_A" : "undefined", "original_A" : undefined }
{ "isNull_A" : false, "type_A" : "double", "original_A" : 1 }
{ "isNull_A" : false, "type_A" : "array", "original_A" : [ ] }
{ "isNull_A" : false, "type_A" : "array", "original_A" : [ null ] }
{ "isNull_A" : false, "type_A" : "array", "original_A" : [ {  } ] }
{ "isNull_A" : false, "type_A" : "array", "original_A" : [ [ ] ] }
{ "isNull_A" : true, "type_A" : "missing" }
{ "isNull_A" : true, "type_A" : "missing" }
{ "isNull_A" : true, "type_A" : "missing" }
{ "isNull_A" : true, "type_A" : "missing" }
{ "isNull_A" : false, "type_A" : "object", "original_A" : { "b" : 1 } }
{ "isNull_A" : false, "type_A" : "object", "original_A" : { "b" : 1, "c" : 1, "d" : 1 } }
{ "isNull_A" : false, "type_A" : "object", "original_A" : { "b" : 1, "c" : 1, "d" : 1 } }
{ "isNull_A" : false, "type_A" : "objectId", "original_A" : ObjectId("5925af23a4365a72f0a568d4") }
{ "isNull_A" : false, "type_A" : "date", "original_A" : ISODate("2017-05-24T16:04:58.266Z") }
{ "isNull_A" : false, "type_A" : "object", "original_A" : { "$id" : ObjectId("5925af40a4365a72f0a568d7"), "$ref" : "coll1" } }

Comment by Asya Kamsky [ 15/Jul/16 ]

In addition, for 3.4 you will be able to use $type to determine the type of the field. I'm sure $isNull can still be useful as syntactic sugar for some expressions, but it just becomes short for

 {$in: [ {$type:"$FIELD"}, [ "missing", "null", "undefined" ] ] } 

Comment by Asya Kamsky [ 15/Jul/16 ]

for array use case originally described, it seems $ifNull:["$possibleArray", [ ] ] should work, no?

{$project:{sizeA:{$size:{$ifNull:["$a",[]]}}}}

will always succeed on array or missing or null field.

Comment by Charlie Swanson [ 04/Nov/15 ]

Given the discussion, I have morphed this ticket into a request for an $isNull expression.

Generally, accumulators (used in the $group stage, e.g. $push, $sum, $avg), are more tolerant of malformed inputs, whereas expressions (used in a couple places, most commonly in $project, e.g. $concat, $add, $isArray) tend to error on malformed inputs.

My understanding is that this will solve all issues, and avoid weird behavior of $size. Let me know if I missed anything.

Comment by Asya Kamsky [ 15/Jul/15 ]

$size probably should continue behaving as it currently does, but it would be nice to have an $isNull test to go with $isArray to allow projecting size of 0 or 1 for non-arrays depending on whether there's a value there or not.

Generated at Thu Feb 08 03:48:39 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.