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.
RSS Feed
