mongodb

mongodb-query

aggregation-framework

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?

Solution 1

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.

Solution 2

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"
}

Solution 3

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" ] }
        }
    }
])