Make string operations on field in mongodb collection (Aggregation & regex)

Question

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 ?

You can try using the $replaceOne aggregate operator (MongoDB v5.x). — prasad_
– prasad_, Commented Nov 2, 2021 at 11:33
$replaceOne would replace the whole document with an other. I'm looking for a way to "format" the price field to remove the "gp", "sp" and use the same money. — Kirk_
– Kirk_, Commented Nov 2, 2021 at 11:45

mohammad Naimi · Accepted Answer · 2021-11-02 15:15:45Z

1

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

edited Nov 2, 2021 at 15:15

answered Nov 2, 2021 at 11:47

mohammad Naimi

2,3591 gold badge7 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kirk_ Over a year ago

This solution is working for basic integers like 5 gp, thought I'm having issues with numbers with a thousand separator like 4,500 gp. Is there a filter to remove the , ?

mohammad Naimi Over a year ago

ok let me update my asnwer

mohammad Naimi Over a year ago

my answer has been updated

Kirk_ Over a year ago

Thanks for your help, is there a way to keep all fields from the initial collection ? (not only price) ?

mohammad Naimi Over a year ago

Could you explain with example?

|

prasad_ · Accepted Answer · 2021-11-02 12:45:29Z

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.

Collectives™ on Stack Overflow

Make string operations on field in mongodb collection (Aggregation & regex)

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related