spring-data-mongo - optional query parameters?

Question

I am using spring-data mongo with the JSON based query methods, and am unsure how to allow optional parameters in a search query.

For instance - say I had the following function

@Query("{ 'name' : {$regex : ?0, $options : 'i'}, 'createdDate' : {$gte : ?1, $lt : ?2 }} }")
List<MyItem> getItemsLikeNameByDateRange(String name, Date startDateRange, Date endDateRange);

-but I didnt want to apply the name regex match, or not apply a date range restriction if NULL values were passed to the method.

At the moment it looks like I might have to build the query using the mongoTemplate.

Are there any alternatives - or is using mongoTemplate the best option?

Thanks

For now I have gone down the route of using the Criteria classes. It does seem a lot cleaner than embedding JSON queries in annotations, and easier to customize which fields are retrieved. — Andrew B
– Andrew B, Commented Jul 24, 2012 at 9:03

Archimedes Trajano · Accepted Answer · 2012-08-29 00:19:46Z

31

To implement this in Boolean logic I do the following and the conversion to operations that are available in programming languages

:query != null -> field == :query
!(:query != null) || (field == :query)
(:query == null) || (field == :query)

In plain SQL, this is done as

where (null = :query) or (field = :query)

In MongoDB this is done through the $where

{ $where: '?0 == null || this.field == ?0' }

We can speed this up a little by using Mongo Operations rather than building everything to the function at the expense of some readability. does not work unfortunately.

{ $or : [ { $where: '?0 == null' } , { field : ?0 } ] }

So what you have is

@Query("{ $or : [ { $where: '?0 == null' } , { field : ?0 } ] }")
List<Something> findAll(String query, Pageable pageable);

This can be further expanded to handle arrays for in/all clauses

@Query("{ $or : [ { $where: '?0.length == 0' } , { field : { $in : ?0 } } ] }")
List<Something> findAll(String query, Pageable pageable);

edited Aug 29, 2012 at 0:19

answered Aug 28, 2012 at 18:06

Archimedes Trajano

43.2k29 gold badges222 silver badges372 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rares Oltean Over a year ago

If your query parameter is of type Date you have to write the $where part like this to work: { $where: '\\'?0\\' == \\'null\\'' }

Paul Lam Over a year ago

Unfortunately, this doesn't work for Page results, because $where is not allowed in the count command. I had to write a custom query method to get the number of the matched documents.

the-bug Over a year ago

In addition to @RaresOltean: as '$where' evaluates JS it might be handy to use string comparison as default. In may case i had a dash in the name which the JS interpreter took as a minus and blow up.

Paul Lam · Accepted Answer · 2021-07-30 14:49:28Z

3

In addition to Archimedes's answer:
If you need the count of matched documents, replace $where with $expr.

@Query("{ $or : [ { $expr: { $eq: ['?0', 'null'] } } , { field : ?0 } ] }")
Page<Something> findAll(String query, Pageable pageable);

answered Jul 30, 2021 at 14:49

Paul Lam

1312 silver badges11 bronze badges

Comments

Christoph Strobl · Accepted Answer · 2022-05-12 09:30:33Z

Filtering out parts of the query depending on the input value is not directly supported. Nevertheless it can be done using @Query the $and and operator and a bit of SpEL.

interface Repo extends CrudRepository<MyItem,...> {

  @Query("""
         { $and : [ 
            ?#{T(com.example.Repo.QueryUtil).ifPresent([0], 'name')}, 
            ?#{T(com.example.Repo.QueryUtil).ifDateBetween([1], [2], 'startDate')},
            ... 
         ]}
         """)
  Page<MyItem> findDataSearchParams(String name, String city, ...)

  class QueryUtil {
    public static Document ifPresent(Object value, String property) {
      if(value == null) {
        return new Document("$expr", true); // always true
      }
      return new Document(property, value); // eq match
    }

    public static Document ifDateBetween(Object value1, Object value2, String property) {
      // ...
    }
  }

  // ...
}

Instead of addressing the target function via the T(...) Type expression writing an EvaluationContextExtension (see: json spel for details) allows to get rid of repeating the type name over and over again.

Collectives™ on Stack Overflow

spring-data-mongo - optional query parameters?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related