Spring data mongodb repository - How to exclude null/blank fields from query using the @Query annotation?

Question

I'm using the Spring-data-mongodb repository framework in my Spring Boot project. I have an API where users can specify 1+ query parameters when searching for an object, and I would like to convert those query parameters to search criteria using the @Query annotation.

Since the number of search fields would not be consistent as users can specify a varying number of query parameters, I cannot hardcode fieldnames like the official documentation examples show. I tried putting together some SpEl strings to do this "dynamic exclusion" of criteria within the @Query annotation, but Spring is escaping the strings I'm returning so I'm unable to concatenate a query together piecemeal. Is there a way to do this using the @Query annotation alone?

(I don't want to declare a repository extension, write Java code calling Spring Mongo DSL methods, nor use the QueryByExample framework. I just want to stick with the @Query annotation)

Dummy sample code:

BookRepo.java:

@Repository
public interface BookRepo extends MongoRepository<Book, String> {
    // Want to make the fields dynamic in this query so that if "author" or "publisher" is null/blank, then it's not included in the query
    @Query("{'author': ?0, 'publisher': ?1}")
    List<Book> getAllBooksBy(String author, String publisher);
}

Gog

Answer 1

This doesn't seem to be openly documented in the Spring-data-mongodb Repository framework docs, but you can convert the entire query to a SpEl expression so that you can dynamically construct queries.

SpEl has the concept of a map which is written like this: {author: 'Joe'}. Maps get converted to standard json queries within the @Query annotation for Spring Mongo repositories, so you could define a little helper method to take in a SpEl map, filter out the blanks, and return that filtered map for the original @Query annotation to use for querying.

First we define a bean with a method to filter out blanks from maps:

QueryHelper.java:

@Component("qHelper")
public class QueryHelper {
    /**
     * Removes blank values from the given map
     */
    public Map<String, Object> noBlanks(Map<String, Object> params) {
        Map<String, Object> filteredMap = new HashMap<>(params);
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            if (StringUtils.isBlank(entry.getValue().toString())) {
                filteredMap.remove(entry.getKey());
            }
        }
        return filteredMap;
    }
}

Then, we adjust our @Query annotation to start with ?#{ and invoke our query helper method with the pre-initialized map of all query fields populated:

BookRepo.java:

@Repository
public interface BookRepo extends MongoRepository<Book, String> {
    @Query("?#{@qHelper.noBlanks( {author: [0], publisher: [1]} )}")
    List<Book> getAllBookBy(String author, String publisher);
}

We can run our Spring Boot server with this argument:

-Dlogging.level.org.springframework.data.mongodb.core.MongoTemplate=DEBUG

And it'll print out the query that actually got executed when author is blank:

find using query: { "publisher" : "Random House"} fields: Document{{}} for class...

Nice! This is quite powerful since you can reuse QueryHelper for other queries with optional parameters. This also works for the project parameter of the @Query annotation, which means you could dynamically convert a theoretical "filter" array of Strings into a projection map based on what a user passes to your API.

Technical note: The code that triggers this behavior seems to be in ParameterBindingJsonReader with this regex they defined: private static final Pattern ENTIRE_QUERY_BINDING_PATTERN = Pattern.compile("^\\?(\\d+)$|^[\\?:]#\\{.*\\}$");

Gog