[SERVER-9576] (Feature) Develop functions for formatting ($format) and mapping ($map) Created: 03/May/13  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Aggregation Framework, Querying
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: Ricardo Planer Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: expression
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:

 Description   

It would be helpful to have formatting and mapping functions in aggregations and queryes.

Formating ex:

{ $format: [ <string_format>, <arg1>, <arg2>, ... ] }
db.data.aggregate( { $group: { _id : { $format: [ "{00:0}/{0000:0}", { $month : "$item.CreatedDate" }, { $year : "$item.CreatedDate" }] } } });

Mapping ex:

{ $map: [ <value>, { <from>, <to> }, { <from>, <to> }, ... ] }
db.data.aggregate( { $group: { _id : { $map: [ { $month : "$item.CreatedDate" }, { 1 : "January" }, { 2, "February" }, ...] } } });

or

{ $map: [ <value>, { <from>, <to> }, { <from>, <to> }, ... ] }
db.data.aggregate( { $group: { _id : { $map: [ { $month : "$item.CreatedDate" }, { 1 : "1º Quarter" }, { 2, "1º Quarter" }, ...] } } });



 Comments   
Comment by Asya Kamsky [ 07/Apr/18 ]

Note that this can be done using existing expressions, so this request would be for simpler syntax.

For $format

db.data.aggregate( {$group:{_id:{$let:{
   vars:{dt:{$dateToParts:{date:"$item.CreatedDate"}}},
   in:{$concat:[ 
      {$cond:[{$gt:["$$dt.month",9]},'','0']}, 
      {$toString:"$$dt.month"}, 
      "/", 
      {$toString:"$$dt.year"}
   ]} 
}}}})

For $map there's several ways to do it, but if you already have an array of mappings in (as an example) this format:

months=[ { from: 1, to : "January" }, { from:2, to: "February" }, {from:3, to: "March"}, {from: 4, to: "April"}, ... ]

You can now do something similar to this:

db.data.aggregate({$group:{
  _id:{$arrayElemAt:[ 
       months.map(x=>x.to), 
       {$indexOfArray:[ 
            months.map(x=>x.from), 
            {$month:"$item.CreatedDate"}
       ]}
  ]},
  sum:{$sum:1}
}})
{ "_id" : "March", "sum" : 1 }
{ "_id" : "January", "sum" : 1 }
{ "_id" : "April", "sum" : 2 }

Note this uses new 3.7.4 $toString function, but previous versions supported the same thing via $substr expression (which when given a number coerce it to a string).

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