$split operator with $ symbol

Working with Data
, , ,
1

I’m trying to split my amount field(String) and remove dollar symbol, for grouping and sum of amount, but I’m getting error saying $ is not valid path, I’m new to MongoDB aggregations and not able to figure out how to split it.

Sample Json:

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

Query:

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

Error:

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'}
2

$ is often used to identify a stage or operator. Try {"$literal": "$"} for a literal $

Many of the fields could be using better data types to begin with and will serve you better as you need to perform different and more complex queries.

1 Like
4

Thanks $literal query is succeeding and I have further pipeline query as below and below is the error

db.collection.aggregate([
  {
    "$project": {
      "MCC": 1,
      "cost_split": {
        "$split": [
          "$Amount",
          {
            $literal: "$"
          }
        ]
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "MCC": 1,
      "cost_split": 1
    }
  },
  {
    "$unwind": {
      "path": "$cost_split",
      "includeArrayIndex": "arrayIndex"
    }
  },
  {
    "$match": {
      "arrayIndex": 1
    }
  },
  {
    "$group": {
      "_id": "$MCC",
      "Total_cost": {
        "$sum": {
          "$convert": {
            "cost_split": "$moop",
            "to": "int"
          }
        }
      }
    }
  }
])

below is the error I’m getting
Error

pymongo.errors.OperationFailure: $convert found an unknown argument: cost_split, full error: {'ok': 0.0, 'errmsg': '$convert found an unknown argument: cost_split', 'code': 9, 'codeName': 'FailedToParse'}
5

Use double or decimal.

You can see how using a correct data type would make this much simpler!