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

Are you familiar with groovy? I have changed the lambda function to closure I am sure you will be able to convert the code to java. If you have any issue comment on the answer. — dsharew, Jun 15 '18 at 13:10

dsharew · Accepted Answer · 2018-07-05T13:43:01.673

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:{

}

score 1 · Answer 2 · answered Jun 21 '18 at 21:57

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();
}

Spring Data MongoDB building dynamic query

2 Answers2