Sam Jamali Sam Jamali - 3 years ago 166
Java Question

Regex Query Mongodb $and operator result issue

I have a MongoDB collection which contains 6 fields in single document, for example:

{
"_id" : ObjectId("59d0f2382043f72a443e6ec0"),
"TranDate" : "2017-07-25T18:01:12+08:00",
"TranCode" : "ActFLA_A01_01_",
"TranValue" : "812.34",
"Seq" : "71",
"Configuration" : "10"
},
{
"_id" : ObjectId("59d0f2332043f72a443e1397"),
"TranDate" : "2017-07-25T18:01:12+08:00",
"TranCode" : "ActFLA_A01_01_",
"TranValue" : "87.34",
"Seq" : "71",
"Configuration" : "10"
},
{
"_id" : ObjectId("59d0f2332043f72a443e1397"),
"TranDate" : "2017-07-25T18:01:12+08:00",
"TranCode" : "ActFLA_A01_01_",
"TranValue" : "828.34",
"Seq" : "71",
"Configuration" : "10"
},
{
"_id" : ObjectId("59d0f2342043f72a443e16be"),
"TranDate" : "2017-07-25T00:45:02+08:00",
"TranCode" : "ActFLA_A01_01_",
"TranValue" : "0.00",
"Seq" : "2",
"Configuration" : "0"
}


Here is my code for query:

Pattern regex = Pattern.compile("^2017-07-25");
Pattern regex2 = Pattern.compile("^ActFLA_A");
Pattern regex3 = Pattern.compile("^10");
DBObject clause1 = new BasicDBObject("TranDate", regex);
DBObject clause2 = new BasicDBObject("TranCode", regex2);
DBObject clause3 = new BasicDBObject("Configuration", regex3);
BasicDBList and = new BasicDBList();
or.add(clause1);
or.add(clause2);
or.add(clause3);
DBObject query = new BasicDBObject("$and", and);


I am getting only one Document but I am expecting 3 Documents which contain (Fields) TranDate, TranCode, TranSeq and Configuration with same value except TranValue.

Answer Source

The question implies that you are interested in returning the (three) documents which meet all of the following conditions:

  • TranDate starts with 2017-07-25
  • TranCode starts with ActFLA_A
  • Configuration starts with 10

The following code will meet that requirement:

@Test
public void aQuery() {
    MongoClient mongoClient = new MongoClientFactory().create();

    MongoCollection<Document> collection = mongoClient.getDatabase("stackoverflow").getCollection("ors");

    Pattern regex = Pattern.compile("^2017-07-25");
    Pattern regex2 = Pattern.compile("^ActFLA_A");
    Pattern regex3 = Pattern.compile("^10");

    Bson clause1 = Filters.regex("TranDate", regex);
    Bson clause2 = Filters.regex("TranCode", regex2);
    Bson clause3 = Filters.regex("Configuration", regex3);

    Bson query = Filters.and(clause1, clause2, clause3);

    Assert.assertEquals(3, collection.count(query));

    // let's have a look at the matched docs ...
    FindIterable<Document> documents = collection.find(query);
    for (Document document : documents) {
        logger.info(document.toJson());
    }
}

Given the sample documents included in the OP, the above test logs the following:

2017-10-01 17:31:59 [main] INFO  c.s.mongo.MongoClientTest - { "_id" : { "$oid" : "59d0f2382043f72a443e6ec0" }, "TranDate" : "2017-07-25T18:01:12+08:00", "TranCode" : "ActFLA_A01_01_", "TranValue" : "812.34", "Seq" : "71", "Configuration" : "10" }
2017-10-01 17:31:59 [main] INFO  c.s.mongo.MongoClientTest - { "_id" : { "$oid" : "59d0f2332043f72a443e1397" }, "TranDate" : "2017-07-25T18:01:12+08:00", "TranCode" : "ActFLA_A01_01_", "TranValue" : "87.34", "Seq" : "71", "Configuration" : "10" }
2017-10-01 17:31:59 [main] INFO  c.s.mongo.MongoClientTest - { "_id" : { "$oid" : "59d11663c26584cd8b7a0ee8" }, "TranDate" : "2017-07-25T18:01:12+08:00", "TranCode" : "ActFLA_A01_01_", "TranValue" : "828.34", "Seq" : "71", "Configuration" : "10" }
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download