query firstname and surname in couchdb

Question

i have two fields firstname and lastname in a document. i want to query if "max smith" or "smith max" exists.

db.find(q)

q.selector['$or'] = [
                        {'$and': [
                            { 'surname': { '$regex': '(?i)max' } },
                            { 'firstname': { '$regex': '(?i)smith' } }
                        ]},
                        {'$and': [
                            {'surname': {'$regex': '(?i)smith'}},
                            {'firstname': {'$regex': '(?i)max'}}
                        ]}
                    ];

and

q.selector['$and'] = [
                        {
                            'surname': {
                                '$regex': '(?i)max|smith'
                            }
                        },
                        {
                            'firstname': {
                                '$regex': '(?i)max|smith'
                            }
                        }
                    ]

but neither finds anything. what am i doing wrong?

Answer 1

I'm no expert at CouchDB, but here's some thoughts from what I could think:

Your First Approach

You are using $or with $and to check for both possible orders of the names. However, it looks like there may be an issue with how you're specifying the regular expressions. Ensure that the field names are correctly matched (i.e., use firstname and surname as appropriate).

q.selector['$or'] = [
    {
        '$and': [
            { 'firstname': { '$regex': '^(?i)max$' } },
            { 'surname': { '$regex': '^(?i)smith$' } }
        ]
    },
    {
        '$and': [
            { 'firstname': { '$regex': '^(?i)smith$' } },
            { 'surname': { '$regex': '^(?i)max$' } }
        ]
    }
];

^(?i)max$ matches "max" case-insensitively from the start (^) to the end ($) of the field value. Same for "smith".

Your Second Approach

I think using a single $regex with a pattern like max|smith will not match the combination of "Max Smith" or "Smith Max" in the two separate fields. Instead, it will look for either "max" or "smith" anywhere in the same field. To find the combination, you need to check each field separately:

q.selector['$and'] = [
    {
        '$or': [
            { 'firstname': { '$regex': '^(?i)max$' } },
            { 'firstname': { '$regex': '^(?i)smith$' } }
        ]
    },
    {
        '$or': [
            { 'surname': { '$regex': '^(?i)max$' } },
            { 'surname': { '$regex': '^(?i)smith$' } }
        ]
    }
];

However, this approach still does not ensure that "Max Smith" or "Smith Max" are specifically matched because it will also match documents where either firstname or surname have "max" or "smith" irrespective of the order imo. Therefore, to correctly find "Max Smith" or "Smith Max", use the $or with $and as shown in the first approach. Ensure that the field names (firstname and surname) are correctly used, and the regex pattern accurately reflects the values you're searching for. If your database is case-sensitive or you want to ensure the regex matches exactly.