A long time ago I wrote a blog post showing how to convert ObjectId value field to corresponding "YYYY-MM" string for reporting type applications. Since I wrote that, aggregation pipeline gained the "$switch" expression which makes the syntax a lot shorter and easier to express (and read).
For variety, this version converts *string* type that represents ObjectId value to corresponding year-month:
For variety, this version converts *string* type that represents ObjectId value to corresponding year-month:
d=[]; o=[]; for (yr=2011; yr < 2018; yr++ ) { for (m=1; m<13; m++) { if (m<10) mo="0"+m; else mo=""+m; var dt=new ISODate(""+yr+"-"+mo+"-01T00:00:00Z"); d.push(""+yr+"-"+mo); /* wrap string in 'new ObjectId()' to convert OID rather than string type */ o.push(""+(dt.getTime()/1000).toString(16)+pad); } } makeLabeledSwitch = function(field, keys, values) { var sw = {$switch:{ "branches":[ ], default:"other"} }; var br=[]; var maxPos=keys.length; var first="<" + keys[0]; br.push({case:{$lt:[field,values[0]]}, then:first}) for (pos = 0; pos < maxPos-1; pos++) { br.push({case:{$lt:[field,values[pos+1]]}, then: keys[pos] }); } var last=">" + keys[maxPos-1]; sw["$switch"]["default"] = last; sw["$switch"]["branches"] = br; return sw; }
This syntax is more straight forward, and what it does is quite similar, which is for every "YYYY-MM" string in the range you're interested in, it maps the range of ObjectId string values (or technically, its first 4-bytes) to the year-month range. If you want to make this work with actual ObjectId type rather than string type, change the loop to populate "o" array with ObjectId() of corresponding string.