在mongoldb中使用$ in String amount字段拆分函数

Question

我试图分割我的金额字段(字符串)并删除美元符号，用于分组和金额总和，但我得到错误说$不是有效的路径

样本数据:

{
"_id": {
"$oid": "637ac16a7c31adec64511551"
},
"User": "0",
"Card": "0",
"Year": "2002",
"Month": "11",
"Day": "26",
"Time": "11:21",
"Amount": "$379.73",
"Use Chip": "Swipe Transaction",
"Merchant Name": "6515854639642454768",
"Merchant City": "Calexico",
"Merchant State": "CA",
"Zip": "92231.0",
"MCC": "3066",
"Errors?": "",
"Is Fraud?": "No"
}

project_cost = {
"$project": {
"MCC": 1,
"cost_split": {
"$split": [
"$Amount", "$"
]
}
}
}

错误:

pymongo.errors.OperationFailure: Invalid $project :: caused by :: '$' by itself is not a valid FieldPath, full error: {'ok': 0.0, 'errmsg': "Invalid $project :: caused by :: '$' by itself is not a valid FieldPath", 'code': 16872, 'codeName': 'Location16872'}

Answer 1

有趣的问题。

数据库将分隔符("$")解释为指示要表达字段的字符。类似于前面的"$Amount"中的$的处理方式，除了这次在字符串中没有其他字符。这会导致您收到的错误:

'$' by itself is not a valid FieldPath

要解决这个问题，可以使用$literal操作符。该运算符有效地允许我们"逃避"。

你的$project看起来像这样:

{
"$project": {
"MCC": 1,
"cost_split": {
"$split": [
"$Amount",
{
$literal: "$"
}
]
}
}
}

这里的游乐场示范

---

编辑一些额外的想法。

如果知道这个字段总是包含一个以$字符作为前缀的字符串，另一种方法是使用$substrBytes操作符:

{
"$project": {
"MCC": 1,
"cost_split": {
"$substr": [
"$Amount",
1,
-1
]
}
}

无论哪种情况，您都需要一个额外的包装器来将其转换为一个数字以供后续求和，例如:

{
"$project": {
"MCC": 1,
"cost_split": {
"$toDouble": {
"$substrBytes": [
"$Amount",
1,
-1
]
}
}
}
}

从长远来看，直接将数据存储为数字可能是值得的。格式化一个数字值以供显示，通常比转换它以供查询和/或计算要便宜得多。