How to query upon the given parameters in Mongo repository

Question

I have a mongo repostiory query as below, If we provide both name and price it gives the response. I want to get response if we only give name or price or both. how to make those parameters optional. if we provide both name and price i want to retrieve aggregated result unless i want to search just from the given field. Much appreciate you help.

List<Response> findByNameAndPrice(String name, int price)

K.D · Answer 1 · 2019-10-17T08:48:46.573

1

Either you may need to implement custom JPA query or need to use QueryDSL in such scenarios.

1) Custom JPA Query like, you may need to change the query if you want to ad new optional parameters.

@Query(value = "{$or:[{name: ?0}, {?0: null}], $or:[{price: ?1}, {?1: null}]}")
List<Response> findByNameAndPrice(String name, Integer price)

2) QueryDSL Approach where you can add as many optional parameters, no need to modify your code. It will generate query automatically.

Refer this link for more : Spring Data MongoDB Repository - JPA Specifications like

edited Oct 17 '19 at 08:48

answered Oct 17 '19 at 06:14

K.D

407
3
10

thanks for the response, I want this implementation using mongo repostiory! – Akalanka Hewawasam Oct 17 '19 at 06:22
@AkalankaHewawasam Updated my answer you can check whether 1st Approach works or not but queryDSL will surely work. – K.D Oct 17 '19 at 06:44
1

I tried the query but it throws and error, Invalid JSON input. Position: 14. Character: '?'. I cannot use query dsl unfortunately since Im using the latest gradle version. – Akalanka Hewawasam Oct 17 '19 at 07:29

dasunse · Answer 2 · 2019-10-18T06:12:24.207

1

I don't believe you'll be able to do that with the method name approach to query definition. From the documentation (reference):

There is a JIRA ticket regarding this which is still under investigation by the Spring team.

You can try this way

In repository

List<Response> findByName(String name)
List<Response> findByPrice(int price)
List<Response> findByNameAndPrice(String name, int price)

In your service file

public List<Response> findByNameAndPrice(String name, int price){
    if(name == null ){
        return repository.findByName(name);
    }
    if( price == 0){
        return repository.findByPrice(price);
    }
    return repository.findByNameAndPrice(name, price);
}

edited Oct 18 '19 at 06:12

answered Oct 18 '19 at 06:06

dasunse

2,839
1
14
32

This approach is obvious. In my scenario, I have like 5 parameters and the "if" chain will be much longer thats why I am looking to sort it out in query level. – Akalanka Hewawasam Oct 20 '19 at 15:02
@AkalankaHewawasam try JPA Criteria API Queries – dasunse Oct 21 '19 at 04:36

score 1 · Accepted Answer · answered Oct 22 '19 at 18:25

Here I found a simple solution using mongodb regex,we can write a query like this, @Query("{name : {$regex : ?0}, type : {$regex : ?1})List<String> findbyNameAndType(String name, String type) The trick is if we want to query upon the given parameter, we should set other parameters some default values. As an example here thinks we only provide name. Then we set the type to select its all possible matches by setting its default param as ".*". This regex pattern gives any string match.

How to query upon the given parameters in Mongo repository

3 Answers3