Mongo replace multiple sub-strings in field in aggregation

user_10634126 0
Viewed 287

I have a bunch of documents like

{'Age': '27 yo'},
{'Age': '81.0'},
{'Age': '15 male'},
{'Age': '25 years old'},
{'Age': 'unknown'}

I want to use a MongoDB aggregation pipeline (in PyMongo) to replace all irrelevant string content in preparation for attempted conversion to integer-like age. Output would be like:

{'Age': '27 yo', 'Age_Standardized': 27},
{'Age': '81.0', 'Age_Standardized': 81},
{'Age': '15 male', 'Age_Standardized': 15},
{'Age': '25 years old', 'Age_Standardized': 25},
{'Age': 'unknown', 'Age_Standardized': None}

Is there a simple way to do this multiple replacement in aggregate?

cursor = db.aggregate([
    {'$match': 
        {match query}
    },
    {'$set': 
        {'Age_Standardized': 
            {replace 'male' with ''},
            {replace 'yo' with ''},
            {replace 'years old' with ''},
        }
    }
])

*Edit: the strings are messier than would permit me to simply extract digits with regexFind. First I would need to run through some functional exclusions. Something equivalent to the below Python function:

    def clean_age(self, s):
        if not any(str.isdigit(char) for char in s): return None
        ign = ['year', 'years', 'yr', 'yrs', 'old', 'yo', 'y.o.', 'y.o', 'male', 'female', '`', '=']
        for word in ign:
            s = s.replace(word, '')
        if '/' in s or re.match(r'\d+-\d+-\d+', s): return None
        if re.match(r'\d{2}\ss', s): s = s.replace('s', '')
        if re.match(r'\d{2}\sfe', s): s = s.replace('fe', '')
        s = s.lstrip('-').rstrip('-')
        s = s.strip()
        try: return int(float(s))
        except: return None
aggregation-framework pymongo mongodb-query mongodb python
1 Answers

Demo - https://mongoplayground.net/p/iLEI2lVhpgF

Use $regexFind to extract numbers from the text

db.collection.aggregate([
  {
    $set: {
      Age_Standardized: {
        $regexFind: { input: "$Age", regex: "[0-9]+" }
      }
    }
  },
  {
    $set: {
      Age_Standardized: {
        $ifNull: [ "$Age_Standardized.match", "None" ]
      }
    }
  }
])
user_22242650
Related