英文:
MongoDB Aggregation - How can I pad an field with zeros and letters on the left?
问题
我想要以下的结果：
[
{
"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"
}
]
英文:
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>
答案1
得分: 2
db.collection.aggregate([
{
$set: {
"trackIngID": {
$concat: [
"TR",
{
"$substrCP": [
{
$toLong: {
$add: [10000000000,"$trackingID"]
}
},
1,
10
]
},
"XP"
]
}
}
}
])
英文:
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"
]
}
}
}
])
答案2
得分: 0
在聚合管道中，你可以执行以下操作：
- 使用
$concat在字符串的开头添加 9 个零。 - 使用
$substrCP剪切掉额外的前导零。使用$strLenCP和$subtract来计算偏移量。 - 使用
$concat添加前缀 'TR' 和后缀 'XP'。
db.collection.aggregate([
{
"$set": {
"trackingID": {
// 使用 9 个零进行填充
"$concat": [
"000000000",
{
$toString: "$trackingID"
}
]
}
}
},
{
"$set": {
"trackingID": {
// 剪切掉额外的前导零
"$substrCP": [
"$trackingID",
{
"$subtract": [
{
"$strLenCP": "$trackingID"
},
9
]
},
9
]
}
}
},
{
"$set": {
"trackingID": {
"$concat": [
"TR",
"$trackingID",
"XP"
]
}
}
}
])
英文:
You can do the followings in an aggregation pipeline:
- pad with 9 zeros at the start of the string with
$concat - trim the extra leading zeros with
$substrCP. Use$strLenCPand$subtractto calculate the offset. $concatwith 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"
]
}
}
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论