I'm trying to project FileName and FileSize for all my files in my collection with a size of 50 mb and greater, but I cannot concat a the type FileSize as it has a type of Int
I want the projection to be
{
"result" : [
{
"_id" : ObjectId("5652c399a21dad0bb01b6308"),
"FileName" : "1234567890.xml",
"FileSize" : "11.06 MB"
},
{
"_id" : ObjectId("5652c399a21dad0bb01b630f"),
"FileName" : "2468101214.xml",
"FileSize" : "320.48 MB"
},
{
"_id" : ObjectId("5652c399a21dad0bb01b631f"),
"FileName" : "3691215180.xml",
"FileSize" : "12.95 MB"
}
}
But so far I can Only return the following
{
"result" : [
{
"_id" : ObjectId("5652c399a21dad0bb01b6308"),
"FileName" : "1234567890.xml",
"FileSize" : 11.0610504150390630
},
{
"_id" : ObjectId("5652c399a21dad0bb01b630f"),
"FileName" : "2468101214.xml",
"FileSize" : 320.4827098846435500
},
{
"_id" : ObjectId("5652c399a21dad0bb01b631f"),
"FileName" : "3691215180.xml",
"FileSize" : 12.9519605636596680
}
}
My query:
db.MyCollection.aggregate(
// Pipeline
[
// Stage 1
{
$match: {
FileSize: {$gte: 5000000}
}
},
// Stage 2
{
$project: {
FileName: 1,
FileSize: {$divide: ["$FileSize", 1048576]}
}
},
// Stage 3
{
$project:{
FileName:1,
FileSize:{$concat:["$FileSize", "MB"]}
}
}
How do I concat the FileSize and "MB" field?
Add Stage 2.5 :P
{
$project:{
FileName:1,
FileSize:{$substr:["$FileSize", 0, -1 ]}
}
}
FileSize is a integer and there is no operation to convert it to String. So you can use hack, and use substr to convert it to string, 0 for starting and -1 rest of the string.
The trick here is to use $substr
to do the conversion to a string, and a few little extras to handle the decimal point precision:
{ "$project": {
"FileName": 1,
"FileSize": {
"$concat": [
{ "$substr": [
{ "$subtract": [ "$FileSize", { "$mod": [ "$FileSize", 1 ] }]},
0,
-1
]},
{ "$substr": [ { "$mod": [ "$FileSize", 1 ] }, 1, 3] },
" MB",
]
}
}}
Or better yet, combine into a single $project
, either with the help of $let
in versions later than MongoDB 2.6, or in long for if needed. A single pipeline stage is more efficient than two:
{ "$project": {
"FileName": 1,
"FileSize": {
"$let": {
"vars": {
"FileSize": { "$divide": [ "$FileSize", 1048576 ] }
},
"in":{
"$concat": [
{ "$substr": [
{ "$subtract": [ "$$FileSize", { "$mod": [ "$$FileSize", 1 ] }]},
0,
-1
]},
{ "$substr": [ { "$mod": [ "$$FileSize", 1 ] }, 1, 3] },
" MB",
]
}
}
}
}}
So as long as you break up the number at the decimal point ( via $mod
) you can throw a "length" argument to the rest of the string to deal with numbers of arbitary length. With the "remainder" separated from the use of $mod
the length of the string to two decimal places is always "three", starting of course from the second position in order to skip the leading 0
.
Returns exactly what you asked for:
{
"_id" : ObjectId("5652c399a21dad0bb01b6308"),
"FileName" : "1234567890.xml",
"FileSize" : "11.06 MB"
}
{
"_id" : ObjectId("5652c399a21dad0bb01b630f"),
"FileName" : "2468101214.xml",
"FileSize" : "320.48 MB"
}
{
"_id" : ObjectId("5652c399a21dad0bb01b631f"),
"FileName" : "3691215180.xml",
"FileSize" : "12.95 MB"
}
It is possible now in MongoDB v4.0 using $toString operator which converts a value to a string:
db.col.aggregate([
{
$project: {
FileSize: { $concat: [ { $toString: "$FileSize" }, " MB" ] }
}
}
])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With