Mongo DB search Query not giving results

user_1367675 0
Viewed 193
db.feedback.find({
  "$or":[
    {"template_id":{"$regex":".*22532.*","$options":"i"}},
    {"user_id":{"$regex":".*22532.*","$options":"i"}}
  ]
})

template_id is a string and user_id is NumberLong data type and It's not finding a result. Its giving result for template_id if I put a correct value, only user_id column seems not responding, Any thoughts?

Sample Data

{ "_id" : ObjectId("5ef21b7de21f8756b67da6e7"), "action_monkey" : 0, "feedback" : false, "more_feedback" : 0, "template_id" : "M23G5RK", "user_id" : 23423 }, /* 2 createdAt:6/15/2020, 3:16:53 PM*/ { "_id" : ObjectId("5ee74a95ae8ac276076db26a"), "action_monkey" : 0, "feedback" : false, "more_feedback" : 0, "template_id" : "M23G5RK", "user_id" : 23375 }, /* 3 createdAt:6/15/2020, 5:55:22 AM*/ { "_id" : ObjectId("5ee6c6faae8ac276076db24b"), "action_monkey" : 0, "feedback" : false, "more_feedback" : 0, "template_id" : "M23G5RK", "user_id" : 23423 }
mongodb-query mongodb
1 Answers

I am not getting any issues with the query.

As you are using $or condition, anyone of one condition should be fallen into true.

I have tried your query, where you are passing .*22532.* the value that is neither in template_id nor in user_id. Hence, you are not getting any data.

Please try with below query

QUERY:

db.getCollection('test1').find(
{ "$or":[
  {"template_id":{"$regex":".*M23.*","$options":"i"}},
  {"user_id":{"$regex":".*23.*","$options":"i"}
 }]
})

The result is coming perfectly.

Results:

/* 1 */
{
    "_id" : ObjectId("5ef21b7de21f8756b67da6e7"),
    "action_monkey" : 0,
    "feedback" : false,
    "more_feedback" : 0,
    "template_id" : "M23G5RK",
    "user_id" : 23423
}

/* 2 */
{
    "_id" : ObjectId("5ee74a95ae8ac276076db26a"),
    "action_monkey" : 0,
    "feedback" : false,
    "more_feedback" : 0,
    "template_id" : "M23G5RK",
    "user_id" : 23375
}

/* 3 */
{
    "_id" : ObjectId("5ee6c6faae8ac276076db24b"),
    "action_monkey" : 0,
    "feedback" : false,
    "more_feedback" : 0,
    "template_id" : "M23G5RK",
    "user_id" : 23423
}

===== EDIT: =====

As user_id is integer value normal regex filter will not work on an integer value. So, we will convert an integer value to a string to apply the regex filter.

db.getCollection('test1').aggregate([
    { "$project": { 
            "user_id_s": { "$toLower": "$user_id" }, 
            "document": "$$ROOT"
        }
     },
     { "$match": {
        "$or":[
            {"document.template_id":{"$regex":".*M23123.*","$options":"i"}},
            {"user_id_s":{"$regex":".*23.*","$options":"i"}}
         ]
     
     }},
    {
        "$replaceRoot": {
            newRoot: "$document"
        }
    }
])
user_93699450
Related