Spring Data MongoDb Query malformed SpEL

Question

Hi I want to run the following query to filter items, the problem is in the second expression where I want to filter the filenames that match some pattern in the list, but it is giving me the following error for that second expression.

Expression [ #fileNames == null or #fileNames.isEmpty() ? {$expr : 'true'} : { $expr: { $gt: [ { $size: { $filter: { input: #fileNames, as: 'pattern', cond: { $regexMatch: { input: '$fileName', regex: '$$pattern' } } } } }, 0 ] } } ] @212: EL1043E: Unexpected token. Expected 'rsquare(])' but was 'comma(,)'

This expression works in mongoDb directly, so what would be the correct way to write this Query?

@Query("{ $and :["
    + "?#{ #users == null or #users.isEmpty() ? {$expr : 'true'} : { 'userName' : {$in : #users} } },"
    + "?#{ #fileNamePatterns== null or #fileNamePatterns.isEmpty() ? {$expr : 'true'} : { $expr: { $gt: [ { $size: { $filter: { input: #fileNamePatterns, as: 'pattern', cond: { $regexMatch: { input: '$fileName', regex: '$$pattern' } } } } }, 0 ] } } },"
    + "?#{ #from == null ? {$expr : 'true'} : { 'timestamp' : {$gte : #from} } },"
    + "?#{ #to == null ? {$expr : 'true'} : { 'timestamp' : {$lte : #to} } }"
    + "]}")
Page<FileHistory> findHistory(List<String> users, List<String> fileNamePatterns, LocalDateTime from, LocalDateTime to, Pageable pageable);

The fields in the entity would be:

public class FileHistory {

  @Id
  private ObjectId fileId;
  private String fileName;
  private String userName;
  private LocalDateTime timestamp;

}

Answer 1

Analysis

The following query part (line) causes the exception:

+ "?#{ #fileNamePatterns== null or #fileNamePatterns.isEmpty() ? {$expr : 'true'} : { $expr: { $gt: [ { $size: { $filter: { input: #fileNamePatterns, as: 'pattern', cond: { $regexMatch: { input: '$fileName', regex: '$$pattern' } } } } }, 0 ] } } },"

Since this query part uses the Spring Expression Language (SpEL for short) — not JSON — it is necessary to respect the SpEL syntax.

The exception message states:

EL1043E: Unexpected token. Expected 'rsquare(])' but was 'comma(,)'

It seems that the SpEL expression parser tries to parse the $gt JSON array as the SpEL indexer expression that accepts the single value: this is why it expects the closing square bracket instead of the comma.

Solution

The solution is made up of the following two changes.

Change #1. Correct the expression according to the SpEL syntax

Please, see the following SpEL-related documentation:

• Spring Framework Documentation 5.3.9: 4. Spring Expression Language (SpEL): 4.3. Language Reference: 4.3.3. Inline Lists.

  Please, note:

  4.3.3. Inline Lists

  You can directly express lists in an expression by using {} notation.

• Spring Framework Documentation 5.3.9: 4. Spring Expression Language (SpEL): 4.3. Language Reference: 4.3.4. Inline Maps.

  Please, note:

  4.3.4. Inline Maps

  You can also directly express maps in an expression by using {key:value} notation.

• Spring Data MongoDB - Reference Documentation: 15. MongoDB Repositories: 15.2. Query Methods: 15.2.6. JSON-based Queries with SpEL Expressions.

  Please, note:

  Expressions can be used to invoke functions, evaluate conditionals, and construct values. SpEL expressions used in conjunction with JSON reveal a side-effect, because Map-like declarations inside of SpEL read like JSON, as the following example shows:

  public interface PersonRepository extends MongoRepository<Person, String> {
  
    @Query("{'id': ?#{ [0] ? {$exists :true} : [1] }}")
    List<Person> findByQueryWithExpressionAndNestedObject(boolean param0, String param1);
  }
  

  Please, note:

  SpEL expressions used in conjunction with JSON

  It is necessary to be careful on where it is SpEL and where it is JSON.

The used map syntax looks correct.

The used list syntax requires the correction:

• Now it is: $gt: […, …].
• It should be: $gt: {…, …}.

For the sake of completeness, here is the entire corrected query part (line):

+ "?#{ #fileNamePatterns == null or #fileNamePatterns.isEmpty() ? {$expr : 'true'} : { $expr: { $gt: { { $size: { $filter: { input: #fileNamePatterns, as: 'pattern', cond: { $regexMatch: { input: '$fileName', regex: '$$pattern' } } } } }, 0 } } } },"

Change #2. Annotate query parameters with @Param annotation

<…>

public interface FileHistoryRepository extends MongoRepository<FileHistory, ObjectId> {
    <…>

    Page<FileHistory> findHistory(
        @Param("users") List<String> users,
        @Param("fileNamePatterns") List<String> fileNamePatterns,
        @Param("from") LocalDateTime from,
        @Param("to") LocalDateTime to,
        Pageable pageable
    );

    <…>
}

Bonus: Draft manual test method

Enable the detailed logging by adding the following line to the src/main/resources/application.properties file:

logging.level.org.springframework.data.mongodb.core.MongoTemplate=DEBUG

Consider using the following draft manual test method as a starting point to test both positive (doc — found) and negative (docx — not found) cases:

private void testRepository(final FileHistoryRepository repository) {
    repository.deleteAll();

    final LocalDateTime now = LocalDateTime.of(2022, 1, 2, 3, 4);
    repository.save(new FileHistory("test.doc", "root", now));

    final Page<FileHistory> positiveHistoryPage = repository.findHistory(
        List.of(),
        List.of("doc"),
        now.minus(1, ChronoUnit.DAYS),
        now.plus(1, ChronoUnit.DAYS),
        Pageable.ofSize(10)
    );
    System.out.println(
        String.format(
            "Positive number of history entries: %d",
            positiveHistoryPage.stream().count()
        )
    );

    final Page<FileHistory> negativeHistoryPage = repository.findHistory(
        List.of(),
        List.of("docx"),
        now.minus(1, ChronoUnit.DAYS),
        now.plus(1, ChronoUnit.DAYS),
        Pageable.ofSize(10)
    );
    System.out.println(
        String.format(
            "Negative number of history entries: %d",
            negativeHistoryPage.stream().count()
        )
    );
}

Running this method produces the following output:

2022-06-30 04:54:16.055 DEBUG 4083 --- [           main] o.s.data.mongodb.core.MongoTemplate      : Remove using query: {} in collection: fileHistory.
2022-06-30 04:54:16.168  INFO 4083 --- [           main] org.mongodb.driver.connection            : Opened connection [connectionId{localValue:3, serverValue:10}] to localhost:27017
2022-06-30 04:54:16.207 DEBUG 4083 --- [           main] o.s.data.mongodb.core.MongoTemplate      : Inserting Document containing fields: [fileName, userName, timestamp, _class] in collection: fileHistory
2022-06-30 04:54:16.267 DEBUG 4083 --- [           main] o.s.data.mongodb.core.MongoTemplate      : find using query: { "$and" : [{ "$expr" : "true"}, { "$expr" : { "$gt" : [{ "$size" : { "$filter" : { "input" : ["doc"], "as" : "pattern", "cond" : { "$regexMatch" : { "input" : "$fileName", "regex" : "$$pattern"}}}}}, 0]}}, { "timestamp" : { "$gte" : { "$date" : "2022-01-01T00:04:00Z"}}}, { "timestamp" : { "$lte" : { "$date" : "2022-01-03T00:04:00Z"}}}]} fields: Document{{}} for class: class the.project.FileHistory in collection: fileHistory
Positive number of history entries: 1
2022-06-30 04:54:16.299 DEBUG 4083 --- [           main] o.s.data.mongodb.core.MongoTemplate      : find using query: { "$and" : [{ "$expr" : "true"}, { "$expr" : { "$gt" : [{ "$size" : { "$filter" : { "input" : ["docx"], "as" : "pattern", "cond" : { "$regexMatch" : { "input" : "$fileName", "regex" : "$$pattern"}}}}}, 0]}}, { "timestamp" : { "$gte" : { "$date" : "2022-01-01T00:04:00Z"}}}, { "timestamp" : { "$lte" : { "$date" : "2022-01-03T00:04:00Z"}}}]} fields: Document{{}} for class: class the.project.FileHistory in collection: fileHistory
Negative number of history entries: 0

Please, note the following actual output entries:

Positive number of history entries: 1
Negative number of history entries: 0

They are the same as the expected ones.