[SERVER-31871] $expr does not seem to work with fields in array Created: 08/Nov/17  Updated: 27/Oct/23  Resolved: 08/Nov/17

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.6.0-rc2
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Henri-Maxime Ducoulombier Assignee: James Wahlin
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

use jira;
 
db.expr.insert({
	"_id":"test",
	"max"{
		"date": ISODate('2017-11-08T00:00:00Z')
	},
	"arr":[
		{'id':1, "date": ISODate('2017-11-01T00:00:00Z')},
		{'id':2, "date": ISODate('2017-11-03T00:00:00Z')},
		{'id':3, "date": ISODate('2017-11-05T00:00:00Z')},
		{'id':4, "date": ISODate('2017-11-07T00:00:00Z')},
		{'id':5, "date": ISODate('2017-11-08T00:00:00Z')},
	],
	'maxdate': ISODate('2017-11-08T00:00:00Z')
});
 
// Works
db.expr.aggregate([	{
	  "$match": {
	    "$expr":
	        {"$eq": ["$maxdate", "$max.date"]
	    }
	    }
	  }
]);
 
// Works
db.expr.aggregate([
	{
		'$project':{"md":{"$max":"$arr.date"}, "maxdate":1},
	},
	{
	  "$match": {
	    "$expr":
	        {"$eq": ["$maxdate", "$md"]
	    }
	    }
	  }
]);
 
// Does not work
db.expr.aggregate([	{
	  "$match": {
	    "$expr":
	        {"$eq": ["$maxdate", "$arr.date"]
	    }
	    }
	  }
]);
 
// Does not work either
db.expr.aggregate([
	{
		'$addFields':{"md":{"$max":"$arr.date"}, "maxdate":1},
	},
	{
	  "$match": {
	    "$expr":
	        {"$eq": ["$maxdate", "$md"]
	    }
	    }
	  }
]);

Participants:
Case:

 Description   

Trying to match a field with another one in an array does not seem to be working.

Also, using $expr to compare a projected field work, but not a new field created with $addFields

Step to reproduce demonstrate the issue.

I'm filling this as a major bug because this looks like important issues in the $expr mechanism to me.



 Comments   
Comment by Henri-Maxime Ducoulombier [ 09/Nov/17 ]

Hello James,

Thank you for all your input.

Actually, my issue with $addFields wasn't one, it was an error in the "steps to reproduce" part. I deeply apologize. It's working with the addition of a field holding the $max of "arr.date" & the comparison of this value.

The dotted fields behavior in aggregation framework is quite disturbing at first, I'll run some tests with the recommendation you gave.

Thank you

Comment by James Wahlin [ 08/Nov/17 ]

Hi Henri-Maxime,

There are several things that come into play with your examples above. They are:

  1. Dotted field paths in aggregation expressions behave differently than in match expressions.
  2. $addFields will not parse the "maxdate":1 entry as an inclusion projection

Dotted field paths in aggregation expressions behave differently than in match expressions

The MongoDB aggregation language dotted field paths will not expand leaf arrays for evaluation. This is in contrast to the match language which will expand and evaluate individual array elements. A good way to confirm this behavior outside of using $expr is to evaluate as part of a project statement. The following illustrates the difference:

> db.test.insert({a: {b: [1,2]}})
WriteResult({ "nInserted" : 1 })
> db.test.aggregate([{$project: {foo: {$eq: ['$a.b', [1,2]]}}}])
{ "_id" : ObjectId("5a034e40fa44e61e38b1ebcc"), "foo" : true }
> db.test.aggregate([{$project: {foo: {$eq: ['$a.b', 1]}}}])
{ "_id" : ObjectId("5a034e40fa44e61e38b1ebcc"), "foo" : false }
> 

What this means is the following is correct to return no results as aggregation will not expand 'arr' and attempt to match against the 'date' field in subobjects.

// Does not work
db.expr.aggregate([	{
	  "$match": {
	    "$expr":
	        {"$eq": ["$maxdate", "$arr.date"]
	    }
	    }
	  }
]);

To make this work, a sub-expression would have to be used to extract the date we want to match against from the 'arr' field. The $reduce expression could be used for this purpose, iterating over the array elements and returning true if the equality is satisfied (of course $max could be used in this context as well):

db.expr.aggregate([{
    "$match": {
        "$expr": {
            $reduce: {
                input: "$arr",
                initialValue: false,
                in : {
                    $cond: {if: "$$value", then: true, else: {$eq: ["$maxdate", "$$this"]}}
                }
            }
        }
    }
}]);

$addfields will not parse the "maxdate":1 entry as a projection

While similar in some ways to $project, $addFields does not parse entries as inclusion/exclusion projections. This field will instead be interpreted as "add field 'maxdate' with a const value of 1" which will then overwrite the original 'maxdate' field. Removing maxdate: 1 from the last example will allow it to match against the 'maxdate' local document field.

Thanks,
James

Comment by Henri-Maxime Ducoulombier [ 08/Nov/17 ]

Affects 3.6.0-rc3 also.

Generated at Thu Feb 08 04:28:27 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.