mongodb java $in query with $regex

Question

I am trying to write $in query with $regex in mongo+java. It's not working in mongo shell either. What I mean is I don't get any results but no query parse error either. Here's the final query I got from Java Debugger at the line where I say collection.find(finalQuery)

{"$and": [ 
    {"$or": [
        {"country": "united states"}
    ]}, 
    {"businesses": {
        "$in": [
            {"$regex": "^.*cardinal.*health.*$"},
            {"$regex": "^.*the.*hartford.*$"}
        ]
    }}
]}

Java Code snipet for Above query:

Set<Pattern> businesses = new HashSet<Pattern>();
            
for(String st: srchTerms) {

    businesses.add(Pattern.compile("^"+st.trim()+"$"));

}
srchTermQuery.append("businesses", new BasicDBObject("$in", businesses));

However, following query works in mongo shell but I don't know how to write it into java:

{"registering_organization": {
    "$in": [
        /^.*cardinal.*health.*$/,
        /^.*the.*hartford.*$/
    ]
}}

Java Code add double quotes around regex if we try to define it as a string.

Answer 1

The behavior you're seeing might be a bug, however as an alternative you can write your query like this

Pattern pattern = Pattern.compile("(^aaa$)|(^bbb$)");
srchTermQuery.append("businesses", pattern);

Not pretty but it seem to do the trick

Answer 2

You're not going to be able to convert:

{"businesses" : {
    "$in":[
        /^.*cardinal.*health.*$/,
        /^.*the.*hartford.*$/
    ]
}}

directly into a Java regex. This is not a bug, it's because the Java driver uses $regex format when creating regex queries to avoid any ambiguity.

The $regex documentation states that

db.collection.find({field: /acme.*corp/});
db.collection.find({field: {$regex: 'acme.*corp'}});

So your Java-generated query of:

{"businesses": {
    "$in": [
        {"$regex": "^.*cardinal.*health.*$"}, 
        {"$regex": "^.*the.*hartford.*$"}
    ]
}}

is exactly equivalent of the query you were trying to convert:

{"businesses": {
    "$in": [
        /^.*cardinal.*health.*$/,
        /^.*the.*hartford.*$/
    ]
}}

In summary, the Java you've written is already the correct way to convert the query you wanted. I've run it in my own test and it returns the expected results.

Perhaps if you included some sample documents that you expect to be returned by the query we could help further?