Lower field for all records in mongo

Basically I have the accounts setup like this:

{
 'email': 'Matt@GMAIL.com',
 'others': '...'
}

In sql, I was used to just searching where LOWER(email) = LOWER(search), however I found that can't easily (and quickly) be done in mongo.

So I'm trying to do something like:

UPDATE accounts SET email = LOWER(email);

or:

accounts.update_many({}, {
            '$set': {
                'email': lower(email)
            }
        }, upsert=False)

Any idea how to do this in Mongo?

PS: I'm using PyMongo driver

2 answers

  • answered 2018-01-16 23:00 oste-popp

    I missunderstood your question.. i can only purpose a python function :)

    def accounts_to_lower():
        for account in accounts.find({'email': {'$exists': True}):
            accounts.update_one({'email': account.get('email'}, {
                '$set': {
                    'email': account.get('email'}.lower()
                }
            }, upsert=False)
    

  • answered 2018-01-16 23:52 IMCoins

    I have made a cleaner code for you. :)

    def add_user_to_table(table, username, **kwargs):
        """ Add user to given table, with given arguments.
        """
        data = { 'name' : username }
        for key, value in kwargs.iteritems():
            data[key] = value
        table.insert(data)
    
    def update_query_in_table(table, query, action='$set',
            multi=False, **kwargs):
        """ Updates user information using **kwargs.
            Either updates using action='$set' or deletes using action='$unset'
        """
        update = {action : {}}
        for key, value in kwargs.iteritems():
            update[action][key] = value
    
        upd = table.update(query, update, multi=multi)
    
    if __name__ == '__main__':
        """ Initializing Database
        """
        client = pymongo.MongoClient('localhost', 27017)
        database = client.connectSysDatabase
        userTable = database.users
    
        # Deletes everything at every run, this is a test program, it won't
        # affects us much here to delete and re-add for the example purpose.
        userTable.delete_many({})
    
        # Creating our Database
        add_user_to_table(userTable, 'John', grade='member')
        add_user_to_table(userTable, 'Kilian', grade='member')
        add_user_to_table(userTable, 'Raphael', grade='member')
    
        # Querying all the 'member' in 'grade'.
        query = { 'grade' : 'member' }
    
        # Will update only one grade to 'Captain'.
        update_query_in_table(userTable, query, multi=False, grade="Captain")
    
        # Will update all grade to 'Captain'.
        update_query_in_table(userTable, query, multi=True, grade="Captain")