AEM JCR SQL2 Full Text Search Case Insensitive

One of the key features of AEM is its support for searching and querying content stored in its Java Content Repository (JCR). In this article, we will explore how to perform a case-insensitive full-text search using the JCR_SQL2 query language in AEM and provide a practical example of achieving this through a servlet.

Performing Case-Insensitive Full Text Search

A case-insensitive full-text search is a common requirement when you want to retrieve content regardless of the letter casing used in the search query. In AEM, the JCR_SQL2 query language is used to interact with the JCR repository. To perform a case-insensitive full-text search, you can utilize theLOWER()function to convert both the search query and the content being searched to lowercase. This ensures that the search is not case-sensitive.

Example Query Code

Consider a scenario where you want to search for assets that are descendants of a specific path and meet certain criteria, while also performing a case-insensitive search for specific keywords. Below is an example of a JCR_SQL2 query code block that achieves this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.json.JSONArray;
import org.json.JSONObject;

import javax.servlet.Servlet;
import java.io.IOException;
import javax.jcr.query.Query;
import javax.jcr.Session;
import javax.jcr.Node;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;
import javax.jcr.query.RowIterator;

@Component(
service = { Servlet.class },
property = {
"sling.servlet.methods=GET",
"sling.servlet.paths=/bin/mysearchservlet"
}
)
public class MySearchServlet extends SlingAllMethodsServlet {

@Override
protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) throws IOException {
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");

// Retrieve the search query parameter
String queryParameter = request.getParameter("query");

// Validate if the query parameter is not null or empty
if (queryParameter == null || queryParameter.isEmpty()) {
JSONObject errorResponse = new JSONObject();
errorResponse.put("error", "Missing query parameter");
response.getWriter().write(errorResponse.toString());
return;
}

// Retrieve the JCR session
Session session = request.getResourceResolver().adaptTo(Session.class);

try {
// Define your query
String queryString = "SELECT * FROM [cq:Page] AS page " +
"WHERE ISDESCENDANTNODE(page, '/content/myproject')" +
" AND (" +
" LOWER(page.[jcr:content/jcr:title]) LIKE '%" + queryParameter + "%'" +
" OR LOWER(page.[jcr:content/cq:template]) LIKE '%" + queryParameter + "%'" +
" OR LOWER(page.[jcr:content/jcr:description]) LIKE '%" + queryParameter + "%'" +
" )" +
"ORDER BY [jcr:score] DESC";

// Get the QueryManager and create the query
QueryManager queryManager = session.getWorkspace().getQueryManager();
Query query = queryManager.createQuery(queryString, Query.JCR_SQL2);

// Execute the query
QueryResult result = query.execute();

// Process the results and build JSON response
JSONArray resultsArray = new JSONArray();
RowIterator rows = result.getRows();
while (rows.hasNext()) {
Node pageNode = rows.nextRow().getNode("page");
JSONObject pageData = new JSONObject();
pageData.put("title", pageNode.getProperty("jcr:content/jcr:title").getString());
pageData.put("template", pageNode.getProperty("jcr:content/cq:template").getString());
pageData.put("description", pageNode.getProperty("jcr:content/jcr:description").getString());
resultsArray.put(pageData);
}

// Respond with JSON search results
response.getWriter().write(resultsArray.toString());
} catch (Exception e) {
JSONObject errorResponse = new JSONObject();
errorResponse.put("error", e.getMessage());
response.getWriter().write(errorResponse.toString());
return;
} finally {
// Close the JCR session
session.logout();
}
}
}

Example Try Catch

When utilizing service users in Java, it’s essential to incorporate try-catch blocks for exception handling. Java introduced enhanced try-catch blocks, which allow you to catch multiple exceptions using a single catch block, in version 7. Before Java 7, try-catch blocks could only handle one exception type per catch block. With the improvements in Java 7, developers gained the ability to efficiently catch and manage various types of exceptions within a structured framework. These enhanced try-catch blocks serve as a powerful means of error management, preventing application crashes, facilitating user-friendly error messages, assisting in the debugging process, and bolstering overall code reliability.

Moreover, when working with service users, it’s important to remember that the JCR session should always be properly closed. Failing to close the session can lead to lingering threads and resource leaks. Incorporating try-with-resources or explicit session closure ensures that resources are released and system resources are utilized optimally. This practice contributes to maintaining the stability and efficiency of your application.

import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.apache.sling.api.resource.ResourceResolver;
import org.apache.sling.api.resource.ResourceResolverFactory;
import org.json.JSONArray;
import org.json.JSONObject;

import javax.servlet.Servlet;
import java.io.IOException;
import java.util.Collections;
import java.util.Map;

@Component(
service = { Servlet.class },
property = {
"sling.servlet.methods=GET",
"sling.servlet.paths=/bin/mysearchservlet"
}
)
public class MySearchServlet extends SlingAllMethodsServlet {

@Reference
private ResourceResolverFactory resolverFactory;

@Override
protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) throws IOException {
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");

// Retrieve the search query parameter
String queryParameter = request.getParameter("query");

// Validate if the query parameter is not null or empty
if (queryParameter == null || queryParameter.isEmpty()) {
JSONObject errorResponse = new JSONObject();
errorResponse.put("error", "Missing query parameter");
response.getWriter().write(errorResponse.toString());
return;
}

final Map<String, Object> authInfo = Collections.singletonMap(
ResourceResolverFactory.SUBSERVICE,
"sourcedCodeSystemUser");

try (ResourceResolver resourceResolver = resolverFactory.getServiceResourceResolver(authInfo)) {
// Define your query
String queryString = "SELECT * FROM [cq:Page] AS page " +
"WHERE ISDESCENDANTNODE(page, '/content/myproject')" +
" AND (" +
" LOWER(page.[jcr:content/jcr:title]) LIKE '%" + queryParameter + "%'" +
" OR LOWER(page.[jcr:content/cq:template]) LIKE '%" + queryParameter + "%'" +
" OR LOWER(page.[jcr:content/jcr:description]) LIKE '%" + queryParameter + "%'" +
" )" +
"ORDER BY [jcr:score] DESC";

// Get the QueryManager and create the query
QueryManager queryManager = resourceResolver.adaptTo(QueryManager.class);
Query query = queryManager.createQuery(queryString, Query.JCR_SQL2);

// Execute the query
QueryResult result = query.execute();

// Process the results and build JSON response
JSONArray resultsArray = new JSONArray();
RowIterator rows = result.getRows();
while (rows.hasNext()) {
Node pageNode = rows.nextRow().getNode("page");
JSONObject pageData = new JSONObject();
pageData.put("title", pageNode.getProperty("jcr:content/jcr:title").getString());
pageData.put("template", pageNode.getProperty("jcr:content/cq:template").getString());
pageData.put("description", pageNode.getProperty("jcr:content/jcr:description").getString());
resultsArray.put(pageData);
}

// Respond with JSON search results
response.getWriter().write(resultsArray.toString());
} catch (Exception e) {
JSONObject errorResponse = new JSONObject();
errorResponse.put("error", "An error occurred while processing the request.");
response.getWriter().write(errorResponse.toString());
return;
}
}
}

Curl Example:

1	curl "http://localhost:4502/bin/mysearchservlet?query=my_search_keyword"

Output:

1
2
3
4
5
6
7
8
9
10
11
12

[
{
"title": "Page Title 1",
"template": "/conf/phonak/settings/wcm/templates/mytemplate",
"description": "This is the description of Page 1"
},
{
"title": "Page Title 2",
"template": "/conf/phonak/settings/wcm/templates/anothertemplate",
"description": "Description for Page 2"
}
]

In this example, theLOWER()function is applied to both the properties being searched and the search queryfulltext This ensures that the search is case-insensitive, allowing you to retrieve relevant assets regardless of letter casing.

Practical Example: Implementing the Search in a Servlet

To practically implement the above query within a servlet in AEM, you would typically perform the following steps:

Create a Java servlet that extendsSlingAllMethodsServletor implementsSlingServlet

In the servlet’sdoGetordoPostmethod, create an instance of theQueryManagerto execute the JCR_SQL2 query.

Use the query string from the example above, replacing placeholders likeproductCFsRootPathandfulltextwith actual values.

Process the query result, extract the relevant data, and respond to the client with the search results.

Conclusion

Performing a case-insensitive full-text search using the JCR_SQL2 query language in Adobe Experience Manager allows you to provide a seamless search experience for users regardless of their choice of letter casing. By utilizing theLOWER()function, you can effectively eliminate case sensitivity from your search queries and retrieve the most relevant content. Incorporating this technique into your AEM projects can enhance user experience and ensure that content discovery is efficient and user-friendly.

Looking for Help? Or need a mentor?

If you need help to super drive your AEM career road map, you can hire me for a one-hour session on codementor.io; We can work together on a price works just for you. https://www.codementor.io/@briankasingli. I'd be happy to help you along your way to becoming a high-quality AEM full-stack engineer.

Was this post helpful?

Let us know if you liked the post. That’s the only way we can improve.

AEM JCR SQL2 Full Text Search Case Insensitive