Form MongoDB Complex Query Using Java

Question

Want to form below MongoDB query using java driver, i have tried couple ways but i couldn't make.

**MongoDB Shell:**
db.collection.find( 
{ "$and" : [ { "abc" : 1} ,
    { $or : [
              { $and : [ { "id.filed1" : "01234" }, { " id.filed2 " : "0123456789" } ] },
              { $and : [ { " id.filed1" : "02222" }, { " id.filed2" : "0422220098" } ] },  
              { $and : [ { " id.filed1" : "01112" }, { " id.filed2" : "0444440005" } ] }
            ]
    } ]})


  Equavlient SQL: 
  Select * from table 
  where visibility = 1 
  AND (
          (filed1= “01234” AND filed2 = “0123456789”) 
       Or (filed1= “01122” AND filed2 = “0408100098”) 
       Or (filed1= “01122” AND filed2 = “0415000005”)
      )


   public static void main(String[] args) {

          String filed11="01234";
          String filed21 ="0123456789";
          String filed12="02222";
          String filed22="0422220098"; 
          String filed13="01112";
          String filed23="0444440005";

          List <UserDetail> ls= new ArrayList<serDetail>();
         UserDetail user1= new UserDetail(filed11,filed21);
         UserDetail user2= new UserDetail (filed12,filed22);
         UserDetail user3= new UserDetail (filed13,filed23);
          ls.add(user1);
          ls.add(user2);
          ls.add(user3);
          req.setSpmUserDetail(ls);


          BasicDBObject whereClause = new BasicDBObject();
          BasicDBObject orQuery = new BasicDBObject();
          BasicDBObject andQuery = new BasicDBObject();
          List<BasicDBObject> andInsideAndQuery = new ArrayList<BasicDBObject>();
          List<BasicDBObject> objAnd = new ArrayList<BasicDBObject>();
          List<BasicDBObject> objOr = new ArrayList<BasicDBObject>();

          objAnd.add(new BasicDBObject(CommonConstants.abc, 1));


          **for (UserDetail userdata: req.UserDetail ()) {
          andInsideAndQuery.add(new BasicDBObject(“filed1”, userdata.filed1()));
          andInsideAndQuery.add(new BasicDBObject(“filed2”, userdata.filed2()));
          orQuery.put("$or", andQuery);
          andQuery.put("$and", andInsideAndQuery);
          }
   orQuery.put("$or", andQuery);
    objAnd.add(orQuery);**

         whereClause.put("$and", objAnd);
          System.out.println(whereClause);
   }

Am not able to form in correct mongodb query using Java.

Getting result like this.

   { "$and" : [ { "visibility" : 1} , { "$or" : [ 
    { "$and" : [ { "idmapping.clientid" : "01234"} , { "idmapping.platforminternalid" : "0123456789"} , 
    { "idmapping.clientid" : "01122"} , { "idmapping.platforminternalid" : "0408100098"} , 
    { "idmapping.clientid" : "01122"} , { "idmapping.platforminternalid" : "0415000005"}]}]}]}

Thank you in advance! Bharathiraja S

Answer 1

You can try the following query.

Note the change to push List<BasicDBObject> andInsideAndQuery = new ArrayList<BasicDBObject>(); inside for loop, which creates multiple $and expressions.

BasicDBObject whereClause = new BasicDBObject();
BasicDBObject orQuery = new BasicDBObject();
BasicDBObject andQuery = new BasicDBObject();
List<BasicDBObject> objOr = new ArrayList<BasicDBObject>();
List<BasicDBObject> objAnd = new ArrayList<BasicDBObject>();

for (UserDetail userdata : req.UserDetail()) {
     List<BasicDBObject> andInsideAndQuery = new ArrayList<BasicDBObject>();
     andInsideAndQuery.add(new BasicDBObject("filed1", userdata.filed1()));
     andInsideAndQuery.add(new BasicDBObject("filed2", userdata.filed2()));
     andQuery.put("$and", andInsideAndQuery);
     objOr.add(andQuery);
}

objAnd.add(new BasicDBObject(CommonConstants.abc, 1));
orQuery.put("$or", objOr);
objAnd.add(orQuery);

whereClause.put("$and", objAnd);

Side note - Inner $and expressions are redundant and can be removed. So both

{ $and : [ { "id.filed1" : "01234" }, { " id.filed2 " : "0123456789" } ] }

same as

{ "id.filed1": "01234", "id.filed2": "0123456789" }