How can I compose a query such as (A || B || C) && (X || Y) in MongoDB Java Driver 3.2

Question

Unfortunately, I cannot find an example for Mongo 3.2 java driver for query like "(A or B or C) and (D or E or F or G)"

Number of parameters inside parentheses will be variable - up to hundred.

Funny thing that I've found example for "(A && B) || (X && Y)" but it doesn't help me.

How to execute queries with both AND and OR clauses in MongoDB with Java

My code produces error:

MongoQueryException: Query failed with error code 2 and error message '$or/$and/$nor entries need to be full objects'

List<Document> docs = new ArrayList<>();

for (Integer ln: input.getLastnames()) {
        docs.add(new Document("lastname",ln));
    }

    Document queryLN = new Document(
            "$or", Arrays.asList(docs)
    );

    docs.clear();
    for (Integer fn: input.getFirstnames()) {
        docs.add(new Document("firstname",fn));
    }

    Document queryFN = new Document(
            "$or", Arrays.asList(docs)
    );

    Document query = new Document(
            "$and", Arrays.asList(queryFN,queryLN));

    List<Document> result = collectionMain.find(query).into(new ArrayList<Document>());

Answer 1

You should use an "in" query in such condition when you have a long unknown list of OR conditions.

An example code:

try {
        MongoClient mongo = new MongoClient();
        DB db = mongo.getDB("so");
        DBCollection coll = db.getCollection("employees");

        List<Integer> ageList = new ArrayList<>();
        ageList.add(30);
        ageList.add(35);

        List<String> nameList = new ArrayList<>();
        nameList.add("Anna");

        BasicDBObject query = new BasicDBObject("$and", Arrays.asList(
            new BasicDBObject("age", new BasicDBObject("$in", ageList)),
            new BasicDBObject("name", new BasicDBObject("$in", nameList)))
        );

        DBCursor cursor = coll.find(query);
        while(cursor.hasNext()) {
            System.out.println(cursor.next());
        }
}catch (Exception ex){
        ex.printStackTrace();
}

To experiment with the above code, you can add the following entries in your MongoDB:

db.employees.insert({"name":"Adma","dept":"Admin","languages":["german","french","english","hindi"],"age":30, "totalExp":10});
db.employees.insert({"name":"Anna","dept":"Admin","languages":["english","hindi"],"age":35, "totalExp":11});
db.employees.insert({"name":"Bob","dept":"Facilities","languages":["english","hindi"],"age":36, "totalExp":14});
db.employees.insert({"name":"Cathy","dept":"Facilities","languages":["hindi"],"age":31, "totalExp":4});
db.employees.insert({"name":"Mike","dept":"HR","languages":["english", "hindi", "spanish"],"age":26, "totalExp":3});
db.employees.insert({"name":"Jenny","dept":"HR","languages":["english", "hindi", "spanish"],"age":25, "totalExp":3});

The above code produces this query:

db.employees.find({"$and":[{"age":{"$in":[30, 35]}},{"name":{"$in":["Anna"]}}]});

And the output is:

{ "_id" : { "$oid" : "57ff3e5e3dedf0228d4862ad"} , "name" : "Anna" , "dept" : "Admin" , "languages" : [ "english" , "hindi"] , "age" : 35.0 , "totalExp" : 11.0}

A good article on this topic: https://www.mkyong.com/mongodb/java-mongodb-query-document/

Read these as well: https://stackoverflow.com/a/8219679/3896066 and https://stackoverflow.com/a/14738878/3896066

Answer 2

Let's first understand your code. We will make it simple by replacing the for loops with simple statements and add some print statements.

List<Document> docs = new ArrayList<>();

docs.add(new Document("lastname","Walker"));
docs.add(new Document("lastname","Harris"));    

Document queryLN = new Document("$or", Arrays.asList(docs));

docs.clear();

System.out.println(queryLN.toJson());//{ "$or" : [[]] } 

docs.add(new Document("firstname", "Pat"));
docs.add(new Document("firstname", "Matt"));

Document queryFN = new Document("$or", Arrays.asList(docs));

System.out.println(queryLN.toJson());//{ "$or" : [[{ "firstname" : "Pat" }, { "firstname" : "Matt" }]] }
System.out.println(queryFN.toJson());//{ "$or" : [[{ "firstname" : "Pat" }, { "firstname" : "Matt" }]] }

Document query = new Document("$and", Arrays.asList(queryFN, queryLN));

System.out.println(query.toJson());//{ "$and" : [{ "$or" : [[{ "firstname" : "Pat" }, { "firstname" : "Matt" }]] }, { "$or" : [[{ "firstname" : "Pat" }, { "firstname" : "Matt" }]] }] }

List<Document> result = collectionMain.find(query).into(new ArrayList<Document>());

Observations:

• docs is already a list. Using Arrays.asList on docs, creates a list of list, which is not acceptable for the $and, $or, $nor. These operators accept a list of Documents. That explains the error message.

• Arrays.asList does not create a copy of the array or the list that it receives. It just creates a wrapper over it. Also, new document() does not copy the list that it receives with "$or", just references the original list. Hence, calling docs.clear() will reset the content in $or of queryLN.

• Also, the above concept explains why the 2nd and 3rd print statements give the same output.

Let us get the code working now.

List<Document> docsLN = new ArrayList<Document>();
List<Document> docsFN = new ArrayList<Document>();

for (Integer ln: input.getLastnames()) {
    docsLN.add(new Document("lastname",ln));
}           

Document queryLN = new Document("$or", docsLN);

for (Integer fn: input.getFirstnames()) {
    docsFN.add(new Document("firstname",fn));
}

Document queryFN = new Document("$or", docsFN);

System.out.println(queryLN.toJson());
System.out.println(queryFN.toJson());

Document query = new Document("$and", Arrays.asList(queryFN, queryLN));

System.out.println(query.toJson());
List<Document> result = collectionMain.find(query).into(new ArrayList<Document>());

Also, consider replacing the $or with $in.

From the docs:

When using $or with that are equality checks for the value of the same field, use the $in operator instead of the $or operator.