I am using a collection in Mongo with a price field with multiple money type :

{
  price: '15 gp' // 15 gold pieces
}

or

{
  price: '4 sp' // 0.4 gold pieces
}

I'm looking for a way to modify this field before querying the collection.

For example doing string modifications to remove the gp/sp and doing math operations to have a correct price in "GP" (1 GP = 10 SP)

This would help ordering the collection since mongo can't understand that 10 sp < 2 gp.

Is there a way to use Aggregation and regex to do it ?

CodePudding user response：

first add new field as rate and check the gp or sp and put rate of each price for example sp have rate of 10 and gp have rate of 1 then add universalprice field with multiply of rate of price and value of price after that you could compare price

db.collection.aggregate([
  {
    "$addFields": {
      "newField": {
        "$split": [
          "$price",
          ","
        ]
      }
    }
  },
  {
    "$project": {
      price: {
        $reduce: {
          input: "$newField",
          initialValue: "",
          in: {
            $concat: [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "rate": {
        "$switch": {
          "branches": [
            {
              "case": {
                "$regexMatch": {
                  "input": "$price",
                  "regex": ".*gp*."
                }
              },
              "then": "1"
            },
            {
              "case": {
                "$regexMatch": {
                  "input": "$price",
                  "regex": ".*sp*."
                }
              },
              "then": "10"
            }
          ],
          default: 1
        }
      }
    }
  },
  {
    "$project": {
      price: 1,
      universalPrice: {
        "$multiply": [
          {
            "$toInt": "$rate"
          },
          {
            "$toInt": {
              $first: {
                "$split": [
                  "$price",
                  " "
                ]
              }
            }
          }
        ]
      }
    }
  }
])

https://mongoplayground.net/p/S5EIUdWRp5W

CodePudding user response：

You can use this aggregation stage to convert the price field value:

db.collection.aggregate([
  { 
      $addFields: { 
          price: {
              $function: {
                  body: function(inPrice) {
                               let outPrice;
                               if (/gp/.test(inPrice)) {
                                   outPrice = parseInt(inPrice.replace("gp", "").trim()) * 10;
                               }
                               else if (/sp/.test(inPrice)) {
                                   outPrice = parseInt(inPrice.replace("sp", "").trim());
                               }
                               else {
                                   outPrice = inPrice; // whatever needs here...
                               }
                               return outPrice;
                  },
                  args: [ "$price" ],
                  lang: "js"
              }
          }
      }
  }
])

For example, price: "5 gp" will convert to price: 50 and price: "12 sp" will convert to price: 12. Note the values are converted to number type fields for comparison and also calculations.