query mongodb with "like" on number of fields

Question

I need to query MongoDB on the number of the fields: name, phone, email. And the query should support "like" syntax: '%s%' What is the better way to perform it:

Query on number of fields with $or
Create array field with values of the above fields and multikey index on this field

An example collection contains the following documents

{
    name: "Evgeny3345",
    phone: "4678946",
    email: "[email protected]"
},
{
    name: "bug",
    phone: "84567521",
    email: "[email protected]"
},
{
    name: "bug2",
    phone: "84567521",
    email: "[email protected]"
 }

When I find all documents with name or phone or email containing "eny", this should return documents 1 and 3.

Do you have some example data to clarify your question with, together with the expected output of the required query? — chridam
Its different because I'm asking what is the best way to query "like" on number of fields. — user1982978

chridam chridam · Accepted Answer · 2015-10-25T15:23:19

Best create a RegExp object with the search pattern and use that with the $or expression that references all the three fields. Something like

var rgx = new RegExp('ny', 'i'),
    query = {
        "$or": [
            { "name": rgx },
            { "phone": rgx },
            { "email": rgx }
        ]
    };
db.collection.find(query)

Sample output:

/* 0 */
{
    "_id" : ObjectId("562cf265d3ea50dcd6085c52"),
    "name" : "Evgeny3345",
    "phone" : "4678946",
    "email" : "[email protected]"
}

/* 1 */
{
    "_id" : ObjectId("562cf265d3ea50dcd6085c54"),
    "name" : "bug2",
    "phone" : "84567521",
    "email" : "[email protected]"
}

query mongodb with "like" on number of fields

1 Answers