Spring Data MongoDB building dynamic query

Question

Need help to build dynamic MongoDB query. everything inside the "$or" Array is dynamic.

db.group.find({ 
        "version" : NumberLong(0), 
        "$or" : [{
                "$and" : [
                    {
                        "object_type" : "D"
                    }, 
                    {
                        "type" : "R"
                    }, 
                    {
                        "name" : "1"
                    }
                ]
            },{
                "$and" : [
                    {
                        "object_type" : "D"
                    }, 
                    {
                        "type" : "E"
                    }, 
                    {
                        "name" : "2"
                    }
                ]
            ]
});

Did the below spring data query but doesn't work

Criteria criteria = Criteria.where("version").is("123");
        List<Criteria> docCriterias = new ArrayList<Criteria>();
        groups.stream().forEach(grp -> {
            docCriterias.add(Criteria.where("type").is(grp.get("type").toString())
                                .andOperator(Criteria.where("object_type").is(grp.get("objectType").toString()))
                                .andOperator(Criteria.where("name").is(grp.get("name").toString())));

        });
        criteria.orOperator((Criteria[]) docCriterias.toArray());
        Query q = new Query(criteria);

Thanks for the help

Answer 1

You should pay attention to how you combine the operators.
The ff code should work for you (note this is groovy remember to change the closure into to java lambda expression):

List<Criteria> docCriterias = new ArrayList<Criteria>();

List groups = [
        [
                type: "type1",
                object_type: "object_type1",
                name: "name1"
        ],

        [
                type: "type2",
                object_type: "object_type2",
                name: "name2"
        ],

        [
                type: "type3",
                object_type: "object_type3",
                name: "name3"
        ],
]

groups.stream().each {grp ->

    docCriterias.add(new Criteria().andOperator(
            Criteria.where("type").is(grp.get("type")),
            Criteria.where("object_type").is(grp.get("object_type")),
            Criteria.where("name").is(grp.get("name"))
    ))


};


Criteria criteria = new Criteria().andOperator(
        Criteria.where("version").is("123"),
        new Criteria().orOperator(docCriterias.toArray(new Criteria[docCriterias.size()]))
);



Query q = new Query(criteria);

Which will give you this query:

{
   "$and":[
      {
         "version":"123"
      },
      {
         "$or":[
            {
               "$and":[
                  {
                     "type":"type1"
                  },
                  {
                     "object_type":"object_type1"
                  },
                  {
                     "name":"name1"
                  }
               ]
            },
            {
               "$and":[
                  {
                     "type":"type2"
                  },
                  {
                     "object_type":"object_type2"
                  },
                  {
                     "name":"name2"
                  }
               ]
            },
            {
               "$and":[
                  {
                     "type":"type3"
                  },
                  {
                     "object_type":"object_type3"
                  },
                  {
                     "name":"name3"
                  }
               ]
            }
         ]
      }
   ]
},
Fields:{

},
Sort:{

}

Answer 2

You could reach this using MongoDB Aggregation Pipeline in Json and Apache Velocity to customize more the Query, then execute this using db.runCommand using Spring MongoTemplate.

Example: monodb_client_dynamic_query.vm

{
    "aggregate":  "client",
    "pipeline": [
        { 
            "$match" : {
                "$and" : [
                    {
                        "is_removed" : {
                            "$ne" : [
                                true
                            ]
                        }
                    }, 
                    {
                        "errors" : {
                            "$size" : 0.0
                        }
                    },
                    {
                        "client_id": "$velocityMap.client_id"
                    }
                ]
            }
        }, 
        { 
            "$project" : {
                "_id" : -1.0, 
                "account" : "$_id.account", 
                "person_id" : "$_id.person_id", 
                "begin_date": { $dateToString: { format: "%Y-%m-%d", date: "$value.begin_date" } },
               "end_date": { $dateToString: { format: "%Y-%m-%d", date: "$value.end_date" } }

            }

        }
     ]
}

Then execute using MondoTemplate:

String script = ...load from file the script monodb_client_dynamic_query.vm
Map parameters = ... put all variables to  replace in the mongodb script
String scriptNoSql = VelocityUtil.loadTemplateVM(script, parameters);
DBObject dbObject = (BasicDBObject) JSON.parse(scriptNoSql);
if (null == dbObject) {
                    return;
}
DB db = mongoTemplate.getDb();
CommandResult result = db.command(dbObject);


if(!result.ok()) {
        throw result.getException();
}