[SERVER-20009] $ifNull doesn't work as expected Created: 18/Aug/15  Updated: 19/Aug/15  Resolved: 18/Aug/15

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

Type: Bug Priority: Minor - P4
Reporter: efkan turen Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Backwards Compatibility: Fully Compatible
Operating System: ALL
Participants:

 Description   

I've been using MongoDB v3.0.3 and I tried the below codes. However $ifNull didn't fill the array. So I can't handle null Array issues;

Aggregation codes;

db.users.aggregate([{ $match: { "_id" : ObjectId("55d31f922d934c7007f048cf") } }, { $project: { 'solv': { $ifNull: [ '$solved', [{ point: 5 }] ] }, code:1 } }, {$group:{ _id:"$_id", score: { $sum: "$solv.point" } }}])

Console output;

{ "_id" : ObjectId("55d31f922d934c7007f048cf"), "score" : 0 }

Another aggregation without $group;

db.users.aggregate([{ $match: { "_id" : ObjectId("55d31f922d934c7007f048cf") } }, { $project: { 'solv': { $ifNull: [ '$solved', [{ point: "0" }] ] } } }])

Console output;

{ "_id" : ObjectId("55d31f922d934c7007f048cf"), "solv" : [ ] }

Not: I've tried by using MongoDB native driver and also Mongo console directly.

An example of Asya Kamsky:
http://stackoverflow.com/a/13898556/3765109



 Comments   
Comment by Asya Kamsky [ 18/Aug/15 ]

$size operator will leave from MongoDB at version 3.2

I'm not sure where you got this impression. $size will continue working in 3.2 just like it does in current version.

3.2 will add some other options. Another workaround for you case that would work now would be to explicitly check for equality with empty array. In any case, the pipeline you showed works exactly as it's supposed to.

Comment by Asya Kamsky [ 18/Aug/15 ]

The $ifNull operator is not supposed to treat empty array as null.

It works as document and as expected.

Comment by efkan turen [ 18/Aug/15 ]

Thanks Charlie,

I change my codes as the below because of $size operator will leave from MongoDB at version 3.2. But still I couldn't understand why $ifNull operator didn't replace the first expression with another like here http://docs.mongodb.org/manual/reference/operator/aggregation/ifNull/#exp._S_ifNull. Anyway. Best regards...

Bar.JavaScript

's': { $cond: [{$eq:['$solved', []] }, [{ point: 0 }], '$solved'] }

Comment by Charlie Swanson [ 18/Aug/15 ]

Sorry, I spoke too soon. I think your $cond solution is what you want. $ifNull will not work, as it will preserve an empty array, so will not fill in the info if it does not exist.

From that answer on stack overflow:

The case it does not handle is "solved" being an empty array - although that would be a simple additional adjustment you could add.

I believe your adjustment is correct. Are there further problems that are unaddressed? Do you have some documents that have an empty array and some which are null or missing? If so, you'll need to add an $ifNull check for those documents, but you'd still need the $cond check for those which were not null but were empty.

Comment by efkan turen [ 18/Aug/15 ]

Hi Charlie,

Assume we have 2 documents in a collection. We want to get a report about all sold goods using aggregation framework.
Needs;
1- We must get all goods(table and armchair)
2- We must get sales qty value of this month if exists
However; we couldn't get armchair if we didn't use this method: http://stackoverflow.com/a/13898556/3765109

{ 
   _id: "55d31f922d934c7007f048cf", 
   name: "table", 
   sales: [ 
        { year: 2015, month: 7, qty: 353 }, 
       { year: 2015, month: 8, qty: 221 }  
   ]
}
{ 
   _id: "55d31f922d934c7007f04895", 
   name: "armchair", 
   sales: [ ]
}

Not: The below alternate codes work. So, $ifNull should have been worked;

 's': { $cond: [{$eq: [{$size: '$solved'}, 0] }, [ { point: 0 } ], '$solved'] }
 

Comment by Charlie Swanson [ 18/Aug/15 ]

Sorry, I think I can gather from your last example that solved is an empty array in the document that matches {_id: ObjectId("55d31f922d934c7007f048cf")}? If that is the case, this is the expected behavior. Asya even points out in her response that her solution would not handle that case. See the docs on $ifNull for the cases in which it will return true.

I think that some new array aggregation operators that are coming in the next release should make this easier in the future. For now, you'd probably have to add an $unwind stage on solv after the project stage, then use the same $group stage you have above. Let me know if that solves your issue!

Thanks,
Charlie

Comment by Charlie Swanson [ 18/Aug/15 ]

Hi Efkan,

Can you please provide some information about the matching document? You assume the $ifNull check to succeed, and solv to become [{point: 5}], right? What is the value of solved in the document that matches {_id: ObjectId("55d31f922d934c7007f048cf")}?

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