Mongodb concat int and string

Question

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?

Hiren S. · Accepted Answer · 2015-11-24 10:58:00Z

39

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.

answered Nov 24, 2015 at 10:58

Hiren S.

2,82017 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abel Over a year ago

The $toString operator was added meanwhile: docs.mongodb.com/manual/reference/operator/aggregation/toString. See below for an answer explaining how to use it.

Blakes Seven · Accepted Answer · 2015-11-24 11:16:55Z

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

The $toString operator was added meanwhile: docs.mongodb.com/manual/reference/operator/aggregation/toString. See below for an answer explaining how to use it.

mickl · Accepted Answer · 2018-06-27 21:00:37Z

21

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

answered Jun 27, 2018 at 21:00

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Collectives™ on Stack Overflow

Mongodb concat int and string

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related