Spring Data MongoDb @Query won't work with more than one $or conditions

Question

I have a strange problem with a simple @Query with spring data and mongodb, the problem is when I use more than one $or condition, it seems that it won't work with 2 or more $or conditions at same time, or I cant put it to work, It only works with one $or at same time, the other is omitted, we can see it in logs and in final result set, where one of the dates ($or) is not filtered :(

mongodb query

db.getCollection('fileStorage').find({
    'user' : { '$ref' : 'user' , '$id' : ObjectId('5bafc1ab40005e285dbafadc')},
    'businessCategory' : { '$ne' : { '$ref' : 'businessCategory', '$id' : ObjectId('5bc0ca4336c9175f7ce6c91d')}},
    'completed' : true,
    'disabled' : false,
    $or : [
        { 'startDateTime' : null },
        { 'startDateTime' : { '$lte' : new Date() } }
    ],
    $or : [
        { 'endDateTime' : null },
        { 'endDateTime' : { '$gte' : new Date() } }
    ]
})

spring data jpa @Query

@Query("{ " +
    "'user' : {'$ref': 'user', '$id': ?#{[0]} }, " +
    "'businessCategory' : { '$ne' : {'$ref': 'businessCategory', '$id': ?#{[1]} } }, " +
    "'completed' : ?#{[2]}, " +
    "'disabled' : ?#{[3]}, " +
    "'$or' : [ { 'startDateTime' : null }, { 'startDateTime' : { '$lte' : ?#{[4]} } } ], " +
    "'$or' : [ { 'endDateTime'   : null }, { 'endDateTime'   : { '$gte' : ?#{[4]} } } ] " +
    "}")
List<FileStorage> getPlayList(ObjectId userId, ObjectId excludeBusinessCategory, boolean completed, boolean disabled, Date currentDateTime, Sort sort);

in logs show

find using query: 
{ "user" : { "$ref" : "user", "$id" : { "$oid" : "5bafc1ab40005e285dbafadc" } }, "businessCategory" : { "$ne" : { "$ref" : "businessCategory", "$id" : { "$oid" : "5bc0ca4336c9175f7ce6c91d" } } }, "completed" : true, "disabled" : false, "$or" : [{ "endDateTime" : null }, { "endDateTime" : { "$gte" : { "$date" : 1540077045932 } } }] }

in above log, we cant see the startDateTime,

"'$or' : [ { 'startDateTime' : null }, { 'startDateTime' : { '$lte' : ?#{[4]} } } ], " +

above startDateTime condition is lost.....

the problem is that I required both $or's but only one works at same time, in above example only works endDateTime,

If I comment endDateTime, it start to work with startDateTime, It wont work either with simple things like that

"'$or' : [ {  } ], '$or' : [ {  } ],"

one of the above $or is simply omitted....maybe its was intentionally and I don't know how to work with it, and I cant find any clue about it in google and documentation

I try many things without success, any help is really appreciated, thanks :)

UPDATED #1: I try e simple repository method to show the problem

@Query with 2 $or

@Query("{" +
  " '$or' : [" +
  "      { 'completed' : null }," +
  "      { 'completed' : false }" +
  "  ]," +
  "  '$or' : [" +
  "      { 'disabled' : null }," +
  "      { 'disabled' : false }" +
  "  ]" +
  "}")
List<FileStorage> findTest();

@Query with 3 $or

@Query("{" +
  " '$or' : [" +
  "      { 'completed' : null }," +
  "      { 'completed' : false }" +
  "  ]," +
  " '$or' : [" +
  "      { 'tested' : null }," +
  "      { 'tested' : false }" +
  "  ]," +
  "  '$or' : [" +
  "      { 'disabled' : null }," +
  "      { 'disabled' : false }" +
  "  ]" +
  "}")
List<FileStorage> findTest();

Log

find using query: { "$or" : [{ "disabled" : null }, { "disabled" : false }] }

same problem. even with only 2 or more simple $or and without parameters, same error, it only use one $or, the other(s) disappear.....

UPDATE #2 : With MongoTemplate and BasicQuery the problem occurs to??????? wtf

String stringQuery = String.format("{\n" +
  "  'user' : { '$ref' : 'user' , '$id' : ObjectId('%s')},\n" +
  "  'businessCategory' : { '$ne' : { '$ref' : 'businessCategory', '$id' : ObjectId('%s')}},\n" +
  "  'completed' : %b,\n" +
  "  'disabled' : %b,\n" +
  "  '$or' : [\n" +
  "    { 'startDateTime' : null },\n" +
  "    { 'startDateTime' : { '$lte' : new Date() } }\n" +
  "  ],\n" +
  "  '$or' : [\n" +
  "    { 'endDateTime' : null },\n" +
  "    { 'endDateTime' : { '$gte' : new Date() } }\n" +
  "  ]\n" +
  "}", userId, businessCategoryId, completed, disabled) ;
BasicQuery query = new BasicQuery(stringQuery);
files = mongoTemplate.find(query, FileStorage.class);

log : same problem it cuts startDateTime

{ "user" : { "$ref" : "user", "$id" : { "$oid" : "5bafc1ab40005e285dbafadc" } }, "businessCategory" : { "$ne" : { "$ref" : "businessCategory", "$id" : { "$oid" : "5bc0ca4336c9175f7ce6c91d" } } }, "completed" : true, "disabled" : false, "$or" : [{ "endDateTime" : null }, { "endDateTime" : { "$gte" : { "$date" : 1540157706740 } } }] }

this problem it's turning into a real pain in the ..., what happens it's all different and possible ways, are always with the same end, spring data won't work with more than one $or, and it cuts some of the query without a reason.......

UPDATE #3

maybe the problem is explained by that image its like $or is the key of a like hashmap, and the last endDateTime replaces the startDateTime :(

in that image I have 4 index keys, the current 4 is the real 5, and I lost the index 4 that is the startDateTime.........

Please check image1 here, and image2 here I dont't have reputation (You need at least 10 reputation to post images.)

enter image description here

UPDATE #4 :

After fighting with Queries and Criterias and find a new problem when use more than one $or.........the nightmare adventure continues eheheeh

org.springframework.data.mongodb.InvalidMongoDbApiUsageException: Due to limitations of the com.mongodb.BasicDocument, you can't add a second '$or' expression specified as '$or

I find a ugly way how to solve this problem, is using above query with BasicQuery, it works but its a lame query, full of DRY, I really don't like it, and I know there is a better way to do that, spring guys are really awesome, and I don't belive that mongo can't work with this kind of queries, that are basic

the above query that works with BasicQuery, have one $or, but full of repeated code, to do the job :(

the query x 4, to work with all conditions

db.getCollection('fileStorage').find({
'$or' : [
    {
        'user' : { '$ref' : 'user' , '$id' : ObjectId('5bafc1ab40005e285dbafadc')},
        'businessCategory' : { '$ne' : { '$ref' : 'businessCategory', '$id' : ObjectId('5bc0ca4336c9175f7ce6c91d')}},
        'completed' : true,
        'disabled' : false,
        'startDateTime' : { '$lte' : new Date() }, 
        'endDateTime' : { '$gte' : new Date() }
    },
    {
        'user' : { '$ref' : 'user' , '$id' : ObjectId('5bafc1ab40005e285dbafadc')},
        'businessCategory' : { '$ne' : { '$ref' : 'businessCategory', '$id' : ObjectId('5bc0ca4336c9175f7ce6c91d')}},
        'completed' : true,
        'disabled' : false,
        'startDateTime' : null,
        'endDateTime' : null        
    },
    {
        'user' : { '$ref' : 'user' , '$id' : ObjectId('5bafc1ab40005e285dbafadc')},
        'businessCategory' : { '$ne' : { '$ref' : 'businessCategory', '$id' : ObjectId('5bc0ca4336c9175f7ce6c91d')}},
        'completed' : true,
        'disabled' : false,
        'startDateTime' : { '$lte' : new Date() }, 
        'endDateTime' : null
    },
    {
        'user' : { '$ref' : 'user' , '$id' : ObjectId('5bafc1ab40005e285dbafadc')},
        'businessCategory' : { '$ne' : { '$ref' : 'businessCategory', '$id' : ObjectId('5bc0ca4336c9175f7ce6c91d')}},
        'completed' : true,
        'disabled' : false,
        'startDateTime' : null, 
        'endDateTime' : { '$gte' : new Date() }
    }    
    ] 
})

the code, with 2 x query, just an example

String stringQuery = String.format("{\n" +
  "'$or' : [{" +
  "  'user' : { '$ref' : 'user' , '$id' : ObjectId('" + userId + "')}," +
  "  'businessCategory' : { '$ne' : { '$ref' : 'businessCategory', '$id' : ObjectId('" + businessCategoryId + "')}}," +
  "  'completed' : " + completed + "," +
  "  'disabled' : " + disabled + "," +
  "  'startDateTime' : { '$lte' : new Date() }," +
  "  'endDateTime' : { '$gte' : new Date() }" +
  "}," +
  "{" +
  "  'user' : { '$ref' : 'user' , '$id' : ObjectId('" + userId + "')}," +
  "  'businessCategory' : { '$ne' : { '$ref' : 'businessCategory', '$id' : ObjectId('" + businessCategoryId + "')}}," +
  "  'completed' : " + completed + "," +
  "  'disabled' : " + disabled + "," +
  "  'startDateTime' : null," +
  "  'endDateTime' : null" +
  "}]" +
  "}", userId, businessCategoryId, completed, disabled);

BasicQuery basicQuery = new BasicQuery(stringQuery/*, "{ user : 1, businessCategory : 1}"*/);
files = mongoTemplate.find(basicQuery, FileStorage.class);

log

{ "$or" : [{ "user" : { "$ref" : "user", "$id" : { "$oid" : "5bafc1ab40005e285dbafadc" } }, "businessCategory" : { "$ne" : { "$ref" : "businessCategory", "$id" : { "$oid" : "5bc0ca4336c9175f7ce6c91d" } } }, "completed" : true, "disabled" : false, "startDateTime" : { "$lte" : { "$date" : 1540249026648 } }, "endDateTime" : { "$gte" : { "$date" : 1540249026648 } } }, { "user" : { "$ref" : "user", "$id" : { "$oid" : "5bafc1ab40005e285dbafadc" } }, "businessCategory" : { "$ne" : { "$ref" : "businessCategory", "$id" : { "$oid" : "5bc0ca4336c9175f7ce6c91d" } } }, "completed" : true, "disabled" : false, "startDateTime" : null, "endDateTime" : null }] }

tomorrow I try to clean up code, and post my conclusions.....I don't really like that code......

but its too strange that we can use raw queries in spring data mongo, too strange!!!! or maybe I don't know how to do it

What exactly are you trying to accomplish with multiple OR statements ? Can you merge them all in one OR ? — Hary, Oct 21 '18 at 21:21
thanks @hary I'm trying to do a simple query like the above query in my post, that works like expected in mongo console this is the only way to get the start and end date, in a data range, and when dates are not defined (both null, or one of then), for that reason I must use both $or's in the query one for each date, I lost a few hours "fighting" and spring data always looses a $or in the process, to strange, I try many things, without success now I try MongoTemplate (**update #2**), and I don' t believe but it cuts the startDateTime too!!!! — Mário Monteiro, Oct 21 '18 at 21:55
Can you merge them all in one OR ? I try that, merge both, it wont work it its a simple query, if I merge it find all 4 conditions and the results don't work, date ranges simply don't work ex if I merge, the same query above, returns 5 records, and not 2...... — Mário Monteiro, Oct 21 '18 at 22:00

score 0 · Answer 1 · answered Oct 22 '18 at 05:52

Is it true that you are trying to fetch documents that match a set criteria like "user": "somevalue" AND "businessCategory": "somevalue" AND {"startdate" is null or less than current date time} and {"enddate" is null or greater than current date time} If query annotation is not working. Try using Criteria and Query classes from spring data mongo in the java method itself. I am just curious to know if the following works for you. If yes then you can play around with Or in the first criteria itself to see if you can combine it there itself instead of writing another criteria and then using OrOperator

    @Autowired
    private MongoOperations cosmosTemplate;

 List<Criteria> citerion = new ArrayList<>();
Criteria criteria = new Criteria();

    criteria = Criteria.where("User")
                    .is("somevalue")
                    .and("businessCategory")                
                    .is("somevalue")
                    .and("completed")
                    .is("somevalue")
                    .and("disbaled")
                    .is("somevalue")
                    .and("startDateTime")
                    .is(null);

       criterion.add(criteria);

       criteria = Criteria.where("User")
                    .is("somevalue")
                    .and("businessCategory")                
                    .is("somevalue")
                    .and("completed")
                    .is("somevalue")
                    .and("disbaled")
                    .is("somevalue")
                    .and("startDateTime")
                    .lte("somedate");

           criterion.add(criteria);



        Query query = Query.query(new Criteria().orOperator(citerion.toArray(new Criteria[citerion.size()]))).with(new Sort(Sort.Direction.ASC,"user"));
         return mongoTemplate.find(queryuery, <<yourclass>>.class);

Thanks @Hary, I'm on work now, but ASAP I will try the 4 diferent method like you sugest. I read again the docs yesterday and it seems a good option, the other is use MongoOperations. In fact I'm surprise why a simple query like this wont work, it really a simple query! Nothing complex at all. I think the best option is use Repository for simple queries and the service for use RAW queries, if it works with RAW queries, this is a must have else its really hard to work with it. But sprint framework is powrfull and always havê a way. I must I admit that I simply dont know how to do it (yet) — Mário Monteiro, Oct 22 '18 at 10:26
thanks for the tips, I try with quereies and criteria, but it seems another dead end, I lost a few more hours, and get the ugly solution for the problem **UPDATE #4**, It's not pretty, and it more a hack than a solution, it solves this specific problem only, the hard way, but don't solves the root of the problem of spring data mongo queries if we must use that type of queries, it seems that is better to move to other type of database or other solution, its a nightmare to hack a simple query in a big block of DRY....... — Mário Monteiro, Oct 22 '18 at 23:28
this seems a million dollar answear.......nobody has any clue about it? — Mário Monteiro, Nov 19 '18 at 21:14

Christopher Bohlen · Answer 2 · 2019-04-29T15:16:06.127

0

For me it worked to make the "and" explicit, like this:

            "{" + //
            "  '$and': [" + //
            "    { 'status': ?0 }," + //
            "    { 'publishedFrom': { $lte: ?1 } }," + //
            "    { '$or': [" + //
            "      { 'publishedUntil': null }," + //
            "      { 'publishedUntil': { $gte: ?1 } }" + //
            "    ] }," + //
            "    { 'interests': { $in: ?2 } }," + //
            "    { '$or': [" + //
            "      { 'tenantNumbers': { $size: 0 } }," + //
            "      { 'tenantNumbers': { $in: ?3 } }" + //
            "    ] }" + //
            "  ]" + //
            "}"

edited Apr 29 '19 at 15:16

answered Apr 29 '19 at 14:51

Christopher Bohlen

11
2

thanks Christopher Bohlen, I only see this answer today, I solved the problem, after some fight with it. and it's very similar with your answer. once again thank you very much :) – Mário Monteiro Aug 22 '19 at 20:53

Spring Data MongoDb @Query won't work with more than one $or conditions

2 Answers2