Question 115429 in Mongodb Question posted in
The official documentation can be found here.

MongoDB Aggregation – How can I pad an field with zeros and letters on the left?

Can you help me with a situation… I Have this Json but I would like to format the field trackingID like "TR000000012345BR". How can I pad a trackingId with letters and zeros on the left and letter on the rigth by aggregate?

[
  {
    "_id": "63f7aad063710fe0106dbc04",
    "name": "Kristen Welch",
    "address": "267 Dooley Street, Westphalia, New York, 1648",
    "trackingID": 963,
    "greeting": "Hello, Kristen Welch! You have 9 unread messages.",
    "favoriteFruit": "apple"
  },
  {
    "_id": "63f7aad0c156afad133a66b6",
    "name": "Shaw Roach",
    "address": "254 Newkirk Placez, Hiseville, American Samoa, 7644",
    "trackingID": 729,
    "greeting": "Hello, Shaw Roach! You have 7 unread messages.",
    "favoriteFruit": "banana"
  }
]

I would like this result below:

[
  {
    "_id": "63f7aad063710fe0106dbc04",
    "name": "Kristen Welch",
    "address": "267 Dooley Street, Westphalia, New York, 1648",
    "trackingID": TR0000000963XP,
    "greeting": "Hello, Kristen Welch! You have 9 unread messages.",
    "favoriteFruit": "apple"
  },
  {
    "_id": "63f7aad0c156afad133a66b6",
    "name": "Shaw Roach",
    "address": "254 Newkirk Placez, Hiseville, American Samoa, 7644",
    "trackingID": TR0000000729XP,
    "greeting": "Hello, Shaw Roach! You have 7 unread messages.",
    "favoriteFruit": "banana"
  }
]

https://mongoplayground.net/p/8uUd7DVcW9R

Tags:

2

Answers


  1. 0

    You can do the followings in an aggregation pipeline:

    1. pad with 9 zeros at the start of the string with $concat
    2. trim the extra leading zeros with $substrCP. Use $strLenCP and $subtract to calculate the offset.
    3. $concat with the prefix ‘TR’ and suffix ‘XP’
    db.collection.aggregate([
  {
    "$set": {
      "trackingID": {
        // pad with 9 zeros
        "$concat": [
          "000000000",
          {
            $toString: "$trackingID"
          }
        ]
      }
    }
  },
  {
    "$set": {
      "trackingID": {
        // trim the extras leading zeros
        "$substrCP": [
          "$trackingID",
          {
            "$subtract": [
              {
                "$strLenCP": "$trackingID"
              },
              9
            ]
          },
          9
        ]
      }
    }
  },
  {
    "$set": {
      "trackingID": {
        "$concat": [
          "TR",
          "$trackingID",
          "XP"
        ]
      }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0 
    db.collection.aggregate([
  {
    $set: {
      "trackIngID": {
        $concat: [                                  //4. concatenate prefix and suffix
          "TR",
          {
            "$substrCP": [                          //3. remove leading 1
              {
                $toLong: {                          //2. convert to long
                  $add: [10000000000,"$trackingID"] //1. add 10000000000, to pad it with zeros
                }
              },
              1,
              10
            ]
          },
          "XP"
        ]
      }
    }
  }
])

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search