Inconsistent results when grouping by possibly missing values

XMLWordPrintableJSON

    • Query Optimization
    • ALL
    • Query 11 (03/14/16)
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      My database contains the following documents:

      use test;
db.test.insert(
	{
	  	'name' : 'first',
	  	'color' : 'blue',
	  	'type' : 'break'
	}
)

db.test.insert(
	{
	  	'name' : 'second',
	  	'color' : 'red',
	  	//'type' : 'break'
	}
)

      Note that the second document does not have a type field, it's missing. The aggregation pipeline produces inconsistent results sometimes showing 'type' :null in the id field, and sometimes not.

      For example, the following aggregation does NOT return the missing type in the key:

      db.test.aggregate([
	{
	  	$group : {
	  	  	'_id' : {
	  	  	 	'name' : '$name',
	  	  	 	'type' : '$type',
	  	  	 	'color' : '$color' 	
	  	  	},
	  	  	'count' : {$sum : 1}
	  	}
	  	
	},
	{
	  	$group : {
	  	  	'_id' : {
	  	  	 	'type' : '$_id.type',
	  	  	 	'color' : '$_id.color' 	
	  	  	},
	  	  	'count' : {$sum : 1}
	  	}
	  	
	}
])

      Results in: 

      { "_id" : { "type" : "break", "color" : "blue" }, "count" : 1 }
{ "_id" : { "color" : "red" }, "count" : 1 }

      However, this aggregation, returns back a 'type' : null,

      db.test.aggregate([
	{
	  	$group : {
	  	  	'_id' : {
	  	  	 	'name' : '$name',
	  	  	 	'type' : '$type',
	  	  	 	'color' : '$color' 	
	  	  	},
	  	  	'count' : {$sum : 1}
	  	}
	  	
	},
	{
	  	$group : {
	  	  	'_id' : {
	  	  	 	'type' : '$_id.type',
	  	  	 	//'color' : '$_id.color' 	
	  	  	},
	  	  	'count' : {$sum : 1}
	  	}
	  	
	}
])

      Results in:

      { "_id" : { "type" : "break" }, "count" : 1 }
{ "_id" : { "type" : null }, "count" : 1 }

      Note that the only difference was the comment out of the 'color' field in the second group.

      The results should be consistent between these two queries. Either the first results should look like:

            Assignee:
            [DO NOT USE] Backlog - Query Optimization
            Reporter:
            Alex Paransky
            Votes:
            1 Vote for this issue
            Watchers:
            25 Start watching this issue

              Created:
              Updated: