Regex Query Mongodb $and operator result issue

Question

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();
and.add(clause1);
and.add(clause2);
and.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.

I would not save dates in string form. This makes database filtering for it harder than necessary. Anyway your code is defect as the clauses are never added to the BasicDBList and. And you should add the code you use for retrieving the matches. May be the problem is there. — JMax
– JMax, Commented Oct 1, 2017 at 16:23

glytching · Accepted Answer · 2017-10-01 16:34:52Z

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" }

Collectives™ on Stack Overflow

Regex Query Mongodb $and operator result issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related