0

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?

TomZ
  • 1
  • 1
  • Why to use Regex? Use something like this: `db.mycollection.find({"IMAGE URL":{$ne:null}});` More [here](https://stackoverflow.com/questions/4057196/how-do-you-query-for-is-not-null-in-mongo) – Maciej Los Feb 04 '23 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 Feb 04 '23 at 10:30
  • Pretty sure `"$regex"` will only try to match strings. Is replacing `null` with `""` (empty string) in the docs an option? – rickhg12hs Feb 05 '23 at 08:14

1 Answers1

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

Noel
  • 10,152
  • 30
  • 45
  • 67