how to write a query where it uses two fields in the document

Question

The title is not enough maybe. Here let me explain.

Assume I have database structure as

{
    name: "alex",
    age: "21",
    location: "university-alex"
}

I am aware that this database structure is not rational but I just want to show my problem in a shortest way.

I want to get the documents where location includes name field value. Here university-alex includes ,alex so it should return as a result of the query.

What have I done so far? I wrote this query but couldn't get a result.

db.collection.find({location: {$regex: "$name"}})

How can I edit it?

Possible dupe of https://stackoverflow.com/questions/40984829/in-mongodb-how-to-perform-query-based-on-if-one-string-field-contains-another — JohnnyHK, May 05 '17 at 19:34

score 0 · Answer 1 · answered May 05 '17 at 18:24

I think what you're trying to achieve can be done with the $where operator. According to this part of the documentation

db.collection.find({ $where: function() { 
  return (this.location.includes(this.name)); 
} } );

Or you can just pass the JS expression to the find method :

db.collection.find(function() { 
  return (this.location.includes(this.name)); 
});

Hope it helps,
best regards

score 0 · Answer 2 · edited May 23 '17 at 11:54

A part of @boehm_s answer that's viable and quite clear you can also create a multiple field index that you can use to find what you need. This kind of index is very useful if you want to perform a combined query approach, e.g. looking in more than one string field if a passed parameters match their content or is contained into them. You can take a look at this doc page about Text Indexes. I don't know if you are using Mongoose or not but this answer may be useful.

Pay attention to the fact that this approach of mine would return ALL the documents that contain, in one or both field,s the "word" you are looking for.

After the creation of the index on your fields

db.collection.createIndex(
   {
     name: "text",
     location: "text"
   }
 )

Let's suppose you've named this index txtIndex, then, you can do

Mongo Driver for Node Way

. . .

let userProjection = {
    "name": 1,
    "age": 1,
    "location": 1
};

/**
 *  @param req Contains information to find a user
 *  @param req.findMe Contains name concatenated to location 
 */
let findUsers = (req) => {
    letUsers = db.collection('users');
    return new Promise((resolve, reject) => {
        User.findOne({'txtIndex': params.body.findMe}, {fields: userProjection},(err, user) => {
           if (err) {
               return reject({message: 'MongoDB Error', err: err});
           }
           if (!user) {
               return reject({message: 'User not found!'});
           }
               return resolve(user);
        });

    });
}

Mongoose Way

let Users = require('./users-model.js);

/**
  *  @param req Contains information to find a user
  *  @param req.findMe Contains name concatenated to location 
  */
let findUsers = (req) => {
   Users.findOne({txtIndex: req.body.FindMe}).then( function (err, user) {
         if (err) {
           return reject({message: 'MongoDB Error', err: err});
         }
         if (!user) {
           return reject({message: 'User not found!'});
         }
           return resolve(user);
    });    

}

how to write a query where it uses two fields in the document

2 Answers2