Use regex and sorting in MongoDB queries with Spring Boot

Here let us see how to use the regex pattern to fetch the records from MongoDB and how to sort the fetching records in Spring Boot Application. Here I have used mongoTemplate to perform the queries with MongoDB.

We have already seen all the find queries and the criteria queries of MongoDB with Spring Boot Application. If you want to know that Please take a look at Find queries of MongoDB and Criteria queries of MongoDB in Spring Boot.

How to use regex in the MongoDB queries with Spring Boot Application

In MongoDB, $regex provides regular expression capabilities for pattern matching strings in queries. We can fetch the records from MongoDB that matches the regex pattern mentioned in the queries.

We can use regex in the queries in the following ways.

1. Use a regex string like a regex(String regex).
2. Use regex with options like a regex(String regex, String options).

Let us see both the ways one by one. We perform the regex queries with the below sample of data.

The below is the list of data of the "user_profile" collection of MongoDB that is serialized with the java entity ProfileVO.

[
    {
        "_id": "61814e96ece0e82f269e13d0",
        "name": "Abdul",
        "profession": "Doctor",
        "age": 45,
        "gender": "Male"
    },
   {
        "_id": "61814ee8ece0e82f269e13d3",
        "name": "Devi",
        "profession": "Psychiatrist\nDoctor",
        "age": 35,
        "gender": "Female"
    },
    {
        "_id": "61814eb8ece0e82f269e13d2",
        "name": "Baskar",
        "profession": "Engineer",
        "age": 35,
        "gender": "Male"
    },  
   {
        "_id": "61814f27ece0e82f269e13d5",
        "name": "Zanu",
        "profession": "Fashion Designer",
        "age": 28,
        "gender": "Female"
    },  
    {
        "_id": "61814f0bece0e82f269e13d4",
        "name": "Yasar",
        "profession": "Architect",
        "age": 40,
        "gender": "Male"
    },    
    {
        "_id": "61814ea3ece0e82f269e13d1",
        "name": "antony",
        "profession": "Teacher",
        "age": 50,
        "gender": "Male"
    }
]

ProfileVO - Pojo class serialized with MongoDB collection user_profile.

package com.tipstocode.model;
import org.bson.types.ObjectId;
import org.springframework.data.annotation.Id;
import org.springframework.data.mongodb.core.mapping.Document;
@Document(collection  = "user_profile")
public class ProfileVO {	
	@Id
	private ObjectId _id;	
	private String name;	
	private String profession;	
	private int age;	
	private String gender;

	public String get_id() {
		return _id.toHexString();
	}
	public void set_id(ObjectId _id) {
		this._id = _id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}	
	public String getProfession() {
		return profession;
	}
	public void setProfession(String profession) {
		this.profession = profession;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
}

Let us see the examples one by one.

Use regex pattern in the query

Query query = new Query();
query.addCriteria(Criteria.where("name").regex("A"));
return mongoTemplate.find(query, ProfileVO.class);

The above query in the form of regex(String regex) returns the list of records if the field 'name' contains the alphabet capital letter 'A'. It doesn't return the records if the name contains the alphabet small letter 'a'. The output is below.

[
    {
        "_id": "61814e96ece0e82f269e13d0",
        "name": "Abdul",
        "profession": "Doctor",
        "age": 45,
        "gender": "Male"
    }
]

If the same query uses the regex string as the small letter 'a' like below then it returns the list of records if the name contains the alphabet small letter 'a'.

Query query = new Query();
query.addCriteria(Criteria.where("name").regex("a"));
return mongoTemplate.find(query, ProfileVO.class);

The output is below.

[
    {
        "_id": "61814ea3ece0e82f269e13d1",
        "name": "antony",
        "profession": "Teacher",
        "age": 50,
        "gender": "Male"
    },
    {
        "_id": "61814eb8ece0e82f269e13d2",
        "name": "Baskar",
        "profession": "Engineer",
        "age": 35,
        "gender": "Male"
    },
    {
        "_id": "61814f0bece0e82f269e13d4",
        "name": "Yasar",
        "profession": "Architect",
        "age": 40,
        "gender": "Male"
    },
    {
        "_id": "61814f27ece0e82f269e13d5",
        "name": "Zanu",
        "profession": "Fashion Designer",
        "age": 28,
        "gender": "Female"
    }
]

Use regex pattern with prefix expression

If we want to fetch the list of records if the field value starts with some alphabet then we have to use the prefix expression in front of the regex string.

For example, if we want to fetch the records if the name starts with the alphabet capital letter A then we have to use the prefix expression (^) in front of the regex string like below.

Query query = new Query();
query.addCriteria(Criteria.where("name").regex("^A"));
return mongoTemplate.find(query, ProfileVO.class);

The output is below

[
    {
        "_id": "61814e96ece0e82f269e13d0",
        "name": "Abdul",
        "profession": "Doctor",
        "age": 45,
        "gender": "Male"
    }
]

Normally the MongoDB queries are case sensitive so that the above query returns only the records that name starts with the capital letter 'A' not small letter 'a'.

Use the regex with options in the MongoDB queries with Spring Boot Application

We can perform the case insensitive queries in MongoDB by using regex with the option 'i'. Now let us see what are all the options available for the regex query and how to use that.

The below options are available for the regex in MongoDB.

Options	Usage
i	We can perform the case-insensitivity query with regex by using this option.
m	* This option matches the string values in the multiline. For example, the anchor ^ matches the string values in the multiline that starts with the regex string (or) the anchor $ matches the string values in the multiline that ends with the regex string. * It recognizes the new line with the character \n. * This option has no effect if the regex pattern doesn't include anchors (^, $) or the string value doesn't contain newline character \n.
x	It ignores all the whitespaces in the regex pattern. Also, it ignores the comments denoted by the # and ends with the new line character \n in the regex pattern.

Let us see the examples of using regex with options.

Query query = new Query();
query.addCriteria(Criteria.where("name").regex("^A","i"));
return mongoTemplate.find(query, ProfileVO.class);

(or)

Query query = new Query();
query.addCriteria(Criteria.where("name").regex("^a","i"));
return mongoTemplate.find(query, ProfileVO.class);

The above query in the form of regex(String regex, String options) returns the list of records that name starts with the capital letter A (or) small letter a. The output is below

[
    {
        "_id": "61814e96ece0e82f269e13d0",
        "name": "Abdul",
        "profession": "Doctor",
        "age": 45,
        "gender": "Male"
    },
    {
        "_id": "61814ea3ece0e82f269e13d1",
        "name": "antony",
        "profession": "Teacher",
        "age": 50,
        "gender": "Male"
    }
]

The below query uses the regex pattern with the option 'm'.

Query query = new Query();		
query.addCriteria(Criteria.where("profession").regex("^Doctor","m"));
return mongoTemplate.find(query, ProfileVO.class);

The above query checks the field (profession) value in multiline that starts with the regex string 'Doctor'. Here it recognizes the new line with the new line character \n. The output is below.

[
    {
        "_id": "61814e96ece0e82f269e13d0",
        "name": "Abdul",
        "profession": "Doctor",
        "age": 45,
        "gender": "Male"
    },
    {
        "_id": "61814ee8ece0e82f269e13d3",
        "name": "Devi",
        "profession": "Psychiatrist\nDoctor",
        "age": 35,
        "gender": "Female"
    }
]

The below query uses the regex pattern with the option 'x'.

Query query = new Query();
query.addCriteria(Criteria.where("profession").regex("^Doc #123 \ntor #145", "x"));		
return mongoTemplate.find(query, ProfileVO.class);

The above query ignores the whitespaces in the regex pattern and also ignores the string (or) comment that is denoted by # and ends with \n in the regex pattern.

The query regex("^Doc #123 \ntor #145", "x")) is equivalent to the query regex("^Doctor"). The output of the query is below.

[
    {
        "_id": "61814e96ece0e82f269e13d0",
        "name": "Abdul",
        "profession": "Doctor",
        "age": 45,
        "gender": "Male"
    }
]

Perform sorting in MongoDB queries with Spring Boot Application

Sorting is used to sort the fetching records based on the order. The order may be ascending order or descending order.

If the field value is of type string then we can sort the records based on alphabetic order. Here ascending order means it sort the records from (A to Z) and descending means it sort the records from (Z to A).

The below query uses the sorting option to fetch the list of records by alphabetic order.

Query query = new Query();		
query.with(Sort.by(Sort.Direction.ASC,"name"));		
return mongoTemplate.find(query, ProfileVO.class);

output

[
    {
        "_id": "61814e96ece0e82f269e13d0",
        "name": "Abdul",
        "profession": "Doctor",
        "age": 45,
        "gender": "Male"
    },
    {
        "_id": "61814eb8ece0e82f269e13d2",
        "name": "Baskar",
        "profession": "Engineer",
        "age": 35,
        "gender": "Male"
    },
    {
        "_id": "61814ee8ece0e82f269e13d3",
        "name": "Devi",
        "profession": "Psychiatrist\nDoctor",
        "age": 35,
        "gender": "Female"
    },
    {
        "_id": "61814f0bece0e82f269e13d4",
        "name": "Yasar",
        "profession": "Architect",
        "age": 40,
        "gender": "Male"
    },
    {
        "_id": "61814f27ece0e82f269e13d5",
        "name": "Zanu",
        "profession": "Fashion Designer",
        "age": 28,
        "gender": "Female"
    },
    {
        "_id": "61814ea3ece0e82f269e13d1",
        "name": "antony",
        "profession": "Teacher",
        "age": 50,
        "gender": "Male"
    }
]

If you want to sort the records by alphabetic order from (Z to A) then change this Sort.Direction.ASC to Sort.Direction.DESC in the query

If the field value is of type number then we can sort the records from greater to least (or) least to greater by using ascending or descending in the query. The below query does that.

Query query = new Query();		
query.with(Sort.by(Sort.Direction.ASC,"age"));		
return mongoTemplate.find(query, ProfileVO.class);

The above query fetches the list of sorted records based on the age from least to greater. The output is below.

[
    {
        "_id": "61814f27ece0e82f269e13d5",
        "name": "Zanu",
        "profession": "Fashion Designer",
        "age": 28,
        "gender": "Female"
    },
    {
        "_id": "61814ee8ece0e82f269e13d3",
        "name": "Devi",
        "profession": "Psychiatrist\nDoctor",
        "age": 35,
        "gender": "Female"
    },
    {
        "_id": "61814eb8ece0e82f269e13d2",
        "name": "Baskar",
        "profession": "Engineer",
        "age": 35,
        "gender": "Male"
    },
    {
        "_id": "61814f0bece0e82f269e13d4",
        "name": "Yasar",
        "profession": "Architect",
        "age": 40,
        "gender": "Male"
    },
    {
        "_id": "61814e96ece0e82f269e13d0",
        "name": "Abdul",
        "profession": "Doctor",
        "age": 45,
        "gender": "Male"
    },
    {
        "_id": "61814ea3ece0e82f269e13d1",
        "name": "antony",
        "profession": "Teacher",
        "age": 50,
        "gender": "Male"
    }
]

If you want to sort the records based on the age from greater to least then use Sort.Direction.DESC in the query.

Note: Sorting query used here to sort by alphabetic order is case sensitive. (ie) It sorts the records from Capital letter A to Capital letter Z. It doesn't sort the records that name starts with a small letter. Right now I don't know how to do case insensitive sorting. Let me know in the comment section if anybody knows.

I hope you understood how to use the regex pattern with options in the MongoDB queries and how to do sorting in the MongoDB queries with Spring Boot Application. If you want to know pagination with sorting then please take a look at Pagination in Spring Boot application with MongoDB. Thanks!. Keep Reading!.