[FIXED] Passing array of reg expressions to spring based mongo @Query

Issue

I'm using Spring boot with mongodb. I've extended PagingAndSortingRepository repository and added the following function

@Query("{'title':{ $nin: [?0]}}")
List<Item> findItem(String[] exclude);

I want to be able to pass it an array of regular expressions such as /dog/,/cat/,/horse/ to exclude any item that may have one of these in it's title.

The above function does not work because the exclude is converted to a string. How can I pass an array of regular expressions to be able to do the above?

Solution

You can work it out by using a Querydsl predicate in one of your controller method.

Add something like this to your controller:

@RequestMapping(value="/search/findByNameRegexNotIn", method = RequestMethod.GET)
@ResponseBody
public List<Item> findByNameRegexNotIn(@RequestParam(name = "name") List<String> names) {
    // build a query predicate
    BooleanBuilder predicate = new BooleanBuilder();  // comes from the Querydsl library
    for (String name : names) {
            predicate.and(QItem.item.name.contains(name).not()); // the QItem class is generated by Querydsl
    }

    List<Item> items = (List<Item>)repository.findAll(predicate);

    return items;
}

You can of course add a Pageable parameter and return a Page<Item> instead of a List.

Edit: another solution if you use Querydsl for this sole purpose is to override the default bindings of your query parameter.

public interface ItemRepository extends CrudRepository<Item, String>,
    QueryDslPredicateExecutor<Item>, QuerydslBinderCustomizer<QItem> {

    @Override
    default public void customize(QuerydslBindings bindings, QItem item) {
        bindings.bind(item.name).all(
            (path, values) ->  path.matches(StringUtils.collectionToDelimitedString(values, "|")).not());
        // disable query on all parameters but the item name
        bindings.including(item.name);
        bindings.excludeUnlistedProperties(true);
    }
}

The controller method:

@RequestMapping(value="/search/query", method = RequestMethod.GET)
@ResponseBody
public List<Item> queryItems(
        @QuerydslPredicate(root = Item.class) Predicate predicate) {
        List<Item> items = (List<Item>)repository.findAll(predicate);

        return items;
    }

Edit: if you don't wan't to override the default QuerydslBinderCustomizer#customize, you can also implement your own binder and specify it in the controller method.

public interface ItemRepository extends CrudRepository<Item, String>,
    QueryDslPredicateExecutor<Item> {
    ...
}

The controller method:

@RequestMapping(value="/search/query", method = RequestMethod.GET)
@ResponseBody
public List<Item> queryItems(
        @QuerydslPredicate(root = Item.class, bindings = ItemBinder.class) Predicate predicate) {
        List<Item> items = (List<Item>)repository.findAll(predicate);

        return items;
    }

The binder class:

class ItemBinder implements QuerydslBinderCustomizer<QItem> {

@Override
public void customize(QuerydslBindings bindings, QItem item) {
    bindings.bind(item.name).all(
            (path, values) ->  path.matches(StringUtils.collectionToDelimitedString(values, "|")).not()
    );
    bindings.including(item.name);
    bindings.excludeUnlistedProperties(true);
}

}

Edit: for the sake of exhaustivity and those who don't want to hear about Querysl. Using the solution proposed in Spring Data Mongodb Reference.

Define a custom repository interface:

interface ItemRepositoryCustom {

    public Page<Item> findByNameRegexIn(Collection<String> names, Pageable page);

}

Define an custom repository implementation (Impl postfix required!):

public class ItemRepositoryImpl implements ItemRepositoryCustom {

    @Autowired
    private MongoOperations operations;

    @Override
    public Page<Item> findByNameRegexNotIn(Collection<String> names, Pageable pageable) {
        String pattern = StringUtils.collectionToDelimitedString(names, "|");
        // this time we use org.springframework.data.mongodb.core.query.Query instead of Querydsl predicates
        Query query = Query.query(where("name").regex(pattern).not()).with(pageable);

        List<Item> items = operations.find(query, Item.class);
        Page<Item> page = new PageImpl<>(items, pageable, items.size());

        return page;
    }

}

Now simply extend ItemRepositoryCustom:

public interface ItemRepository extends MongoRepository<Item, String>, ItemRepositoryCustom {

...

}

And you're done!

Answered By - Marc Tarin