Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mongodb concat int and string

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?

like image 841
Jaun Lloyd Avatar asked Nov 24 '15 10:11

Jaun Lloyd


3 Answers

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.

like image 200
Hiren S. Avatar answered Sep 24 '22 10:09

Hiren S.


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"
}
like image 24
Blakes Seven Avatar answered Sep 25 '22 10:09

Blakes Seven


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" ] }
        }
    }
])
like image 21
mickl Avatar answered Sep 26 '22 10:09

mickl