1

I'm trying to find all lists which the person number has '-' or '.' inside of it. I already tried this answer, but it's not working for elements inside of the array.

But when I try to find by the entire String, without the regex notation, the document is found.

Per example:

db.getCollection('list').find({"persons.number": "123456789"}) //works!

db.getCollection('list').find({"persons.number": /3/}) //not work...
db.getCollection('list').find({"persons.number": /.*3.*/}) //not work
db.getCollection('list').find({"persons.number": /.*..*/}) //not work
db.getCollection('list').find({"persons.number": /.*[-\.]+.*/}) //not work

If I try to find the document by some attribute outside of the array (an attribute from the list, per example), the /3/, /.*3.*/ and /.*[-\.]+.*/ works.

Document format:

{
    "_id" : ObjectId("5af3037ee8006c4a04e84b2f"),
    "id" : 1,
    "persons" : [ 
        {
            "id" : 1,
            "number" : "123.123.123-22"
        }, 
        {
            "id" : 2,
            "number" : "123.456.789-11"
        }
    ]
}

So, what are the options?

I'm using the MongoDB from Azure. Executing the db.version() on console returns 3.2.0.

Matheus Lacerda
  • 5,983
  • 11
  • 29
  • 45
Dherik
  • 17,757
  • 11
  • 115
  • 164
  • Hi Dherik - could I ask you to double-check your document format? Two reasons why: I've tried with a copy of your example document and the first regex /3/ is working for me; also, your example document doesn't have an _id field at all. – Vince Bowdren May 09 '18 at 13:51
  • You're using Azure Cosmos DB? I tried with MongoDB v3.4.14, accessed through Robo 3T. – Vince Bowdren May 09 '18 at 14:06
  • Yes, `Cosmos`, not `Cloud`... sorry. The returned version from console is `3.2.0` – Dherik May 09 '18 at 14:08
  • Could you copy the JSON of an example document i.e. including the _id and everything exactly as it really is in the database? – Vince Bowdren May 09 '18 at 14:10
  • @VinceBowdren, I update my question with the Json document copied from MongoDB. – Dherik May 09 '18 at 14:21
  • Testing on MongoDB v3.4.14 (not Cosmo DB), with a copy of your example document, all the regexes work just fine e.g. `"persons.number" : /3/` or `"persons.number" : /-/` or `"persons.number" : /\./`. – Vince Bowdren May 09 '18 at 14:28
  • 1
    PS I deleted my earlier comments, as they are obsolete after your clarifications. – Vince Bowdren May 09 '18 at 14:29
  • This is probably a problem from Azure MongoDB (CosmosDB)... I already found some limitations (like lacks of `$text` support). Is there some other way to make this search? – Dherik May 09 '18 at 14:30

2 Answers2

0

The regex .*[-\.]+.* should work,

Try this,

db.getCollection('list').find({"persons.number": /.*[-\.]+.*/})
RaR
  • 3,075
  • 3
  • 23
  • 48
  • Not works... If I try to find the document by some attribute outside of the array (an attribute from the list, per example), the /3/, /.*3.*/ and /.*[-\.]+.*/ works. – Dherik May 09 '18 at 13:40
  • I'm using MongoDB from Azure. – Dherik May 09 '18 at 14:05
0

For searching multiple patterns , i.e an OR of patterns the regex format is little different. 

(pattern1)|(pattern2)

Tried the below mongo query on my local running 3.4.6 , but it should work on 3.2.x as well

db.list.aggregate([{"$unwind":{"path":"$persons"}},{"$project":{"_id":1,"persons.number":1}},{"$match":{"persons.number":{"$regex":/.*(\.)|(\-).*/}}},{"$group":{_id:"$_id"}}])
mintekhab
  • 203
  • 1
  • 3