MongoDB regex query with property that can be null

Question

Writing a MongoDB regex query in Spring with a field that can be null.

I want to query documents by name and phone:

Query(value = "{ {'name' : {$regex:?0,$options:'i'}},
                          {'phone' : {$regex:?1,$options:'i'}} }")
Document findByFullNameOrPhone(String fullName, String phone);

The value I'm passing through the query for phone is ".*" in an attempt to match everything.

It works but the problem is phone is a field that can be null. If the document has no phone value it's not included in the query result. Is it possible to use this query to find all documents in the database, even if the document does not have a value for phone?

Why to use Regex? Use something like this: db.mycollection.find({"IMAGE URL":{$ne:null}}); More here — Maciej Los
– Maciej Los, Commented Feb 4, 2023 at 10:08
Because it's not only used to find all, I also want to be able to find documents where name and/or phone match the value I pass through the regex. — TomZ
– TomZ, Commented Feb 4, 2023 at 10:30
Pretty sure "$regex" will only try to match strings. Is replacing null with "" (empty string) in the docs an option? — rickhg12hs
– rickhg12hs, Commented Feb 5, 2023 at 8:14

Noel · Accepted Answer · 2023-02-06 03:40:03Z

0

Just add null check.

Query(value = "{
  'name': {'$regex': ?0,'options': 'i'},
  $or: [
    {'phone': null},
    {'phone': {'$regex': ?1,'options': 'i'}}
  ]
}")
Document findByFullNameOrPhone(String fullName, String phone);

Demo

answered Feb 6, 2023 at 3:40

Noel

10.6k30 gold badges53 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB regex query with property that can be null

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related