Search for string or number in field

Question

I have about 200k documents in a MongoDB collection and I need to get only the documents that has specific contents.

For example,

{
  "_id": ObjectID("593ddb4f02a8b0e23446ad65"),
  "instance_id": "166143",
  "json": {
    "o": {
      "2": [9]
    }
  }
}

I want to get documents that have 9 in json.o.2 but the problem is, json.o.2 can be an object, it can be a string & also an integer for example I wanna get all these ones

"2": [9]
...
"2": 9
...
"2": "9"
...
"2": ["9"]

I tried to use regex but it didn't work

 'json.o.2': { '$regex': /^(\[?)(.*)(,?)("?)9("?)(,?)(.*)(\]?)$/i } 

I also need to get all documents that don't contain a specific number, I use regex too for that case but it don't work too

'json.o.2': { '$regex': /^([?)([^3]*)(,?)("?)([^3]*)("?)(,?)([^3]*)(]?)$/i } 

Answer 1

Then look for both with $in:

db.collection.find({ "json.o.2": { "$in": ["9",9] } })

The $in operator is basically a shorthand form of $or

db.collection.find({ "$or": [{ "json.o.2": "9" }, { "json.o.2": 9 } ])

The general case is that "either" value is actually searched for and since you have an exact match condition then that is perfectly fine. MongoDB also does not care if the path is a plain value or within an array.

Answer 2

you can easily achieve this without any regex but with the $or operator.

This query should do the work :

db.collection.find({$or: [{"json.o.2": 9}, {"json.o.2": "9"}]}) 

try it online: mongoplayground.net/p/6utj08SDWDU