How to use AEM JCR SQL2 query strings to query for nodes in Java Content Repository

AEM Development Quick Reference Search Sling Servlet Video Tutorial
briankasingli

How to use AEM JCR SQL2 query strings to query for nodes in Java Content Repository

JCR-SQL2 (Java Content Repository – Structured Query Language 2) is domain specific language used to query JCR nodes held in the JCR repository. The syntax for JCR-SQL2 is fairly similar to SQL, so if you have some experience with SQL, this tutorial will be a walk in the park for you.

The best way to learn the power of the JCR-SQL2 language is to try it out on CRX/DE Lite. Without future or do, below will be a lesson on JCR-SQL2. To locate CRX/DE Lite’s query tool, visit http://localhost:4502/crx/de/index.jsp, then select on the tools drop down, and choose query.

2023 Added Section: SQL-JCR2 Snippets

Examples can be found at the bottom of this tutorial, JCR-SQL2 Code Snippets Cheat Sheet, where you can find some examples for real life use cases. Head into your crx-de > tools > query > type:JCR SQL2, and try it our yourself.

JCR-SQL2 Syntax

  1. SELECT Statement
  2. NAME() Statement
  3. ISDESCENDANTNODE Statement
  4. CONTAINS Statement
  5. LIKE Operator
  6. IS NOT NULL Property
  7. ORDER BY Keyword
  8. CAST() Statement
  9. JCR-SQL2 Code Snippets Cheat Sheet (new 2023)
  10. JCR SQL2 Java Code Example Servlet (new 2023)

1. The JCR-SQL2 SELECT Statement

  • The SELECT statement is used to select all JCR nodes that matches the JCR node’s primary type.
  • Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
  • Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
  • Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.
1
2
3
4
5
6
7
8
9
-- nt:base is a primary type that represents every single node in the JCR.
-- returns all nodes in the JCR.
SELECT * FROM [nt:base]

-- returns all [cq:Page] nodes
SELECT * FROM [cq:Page]

-- returns all [dam:Asset] nodes
SELECT * FROM [dam:Asset]




2. The JCR-SQL2 NAME() Statement

  • Selecting nodes with a specific node name.
  • Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
  • Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
  • Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.
1
2
3
4
5
-- returns all [cq:Page] nodes WHERE the node's name is equal to "we-retail"
SELECT * FROM [cq:Page] AS nodes WHERE NAME(nodes) = "we-retail"

-- returns all [dam:Asset] nodes WHERE the node's name is equal to "we-retail"
SELECT * FROM [dam:Asset] AS nodes WHERE NAME(nodes) = "we-retail"

3. The JCR-SQL2 ISDESCENDANTNODE Statement

  • Selecting nodes under a file path.
  • Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
  • Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
  • Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.
1
2
3
4
5
6
7
8
9
10
11
-- returns all nodes WHERE nodes exist under "/content/we-retail"
select * FROM [nt:base] WHERE ISDESCENDANTNODE ([/content/we-retail])

-- returns all [cq:Page] nodes WHERE nodes exist under "/content/we-retail"
SELECT * FROM [cq:Page] WHERE ISDESCENDANTNODE ([/content/we-retail])

-- returns all [dam:Asset] nodes WHERE nodes exist under "/content/dam/we-retail"
SELECT * FROM [dam:Asset] WHERE ISDESCENDANTNODE ([/content/dam/we-retail])

-- returns all [nt:unstructured] nodes WHERE nodes exist under "/content/we-retail"
SELECT * FROM [nt:unstructured] WHERE ISDESCENDANTNODE([/content/we-retail])

4. The JCR-SQL2 CONTAINS Statement

  • Selecting nodes where the properties contain a value.
  • Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
  • Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
  • Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.
1
2
3
4
5
-- returns all nodes WHERE node's property jcr:title CONTAINS "we-retail"
SELECT * FROM [nt:base] AS nodes WHERE CONTAINS(nodes.title, "we-retail")

-- returns all [cq:Page] nodes where node's property jcr:title CONTAINS "we-retail"
SELECT * FROM [cq:Page] AS nodes WHERE CONTAINS(nodes.title, "we-retail")




5. The JCR-SQL2 LIKE Operator

  • The LIKE operator is used to search for a specified pattern in node properties.
  • There are two wildcards used in conjunction with the LIKE operator: % and _
  • Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
  • Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
  • Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- returns all [cq:PageContent] nodes WHERE nodes exist under "/content"
-- AND node's property jcr:title starts with any values and ends with "w"
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] LIKE "%w"

-- returns all [cq:PageContent] nodes WHERE nodes exist under "/content"
-- AND node's property jcr:title starts with "w" and ends with any values
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] LIKE "w%"

-- returns all [cq:PageContent] nodes WHERE nodes exist under "/contentl"
-- AND node's property jcr:title matching "w" in any position
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] LIKE "%w%"

-- returns all [cq:PageContent] nodes WHERE nodes exist under "/content"
-- AND node's property jcr:title start with any values and have "w" in the second position and ends with any values
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] LIKE "_w%"

6. The JCR-SQL2 IS NOT NULL Property

  • Used for validation of property’s value is not null.
  • Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
  • Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
  • Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.
1
2
3
4
5
6
7
8
9
-- returns all nodes WHERE nodes exist under "/content"
-- AND node's property jcr:title IS NOT NULL
SELECT * FROM [nt:base] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] IS NOT NULL

-- returns all [cq:PageContent] nodes WHERE nodes exist under "/content"
-- AND node's property jcr:title IS NOT NULL
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] IS NOT NULL




7. The JCR-SQL2 ORDER BY Keyword

  • The ORDER BY keyword is used to sort the result-set in ascending or descending order.
  • Returned values are sorted by default in ascending order.
  • Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
  • Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
  • Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.
1
2
3
4
5
6
7
8
9
-- returns all nodes WHERE nodes exist under the "/content/we-retail"
-- ORDER BY jcr:created ascending
SELECT * FROM [nt:base] AS nodes WHERE ISDESCENDANTNODE ([/content/we-retail])
ORDER BY nodes.[jcr:created]

-- returns all nodes WHERE nodes exist under the "/content/we-retail"
-- ORDER BY node's property jcr:created descending
SELECT * FROM [nt:base] AS nodes WHERE ISDESCENDANTNODE ([/content/we-retail])
ORDER BY nodes.[jcr:created] DESC

8. The JCR-SQL2 CAST() Statement

  • Convert an expression from one data type to another.
  • Cast Types:
    • STRING
    • BINARY
    • DATE
    • LONG
    • DOUBLE
    • DECIMAL
    • BOOLEAN
    • NAME
    • PATH
    • REFERENCE
    • WEAKREFERENCE
    • URI
  • Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
  • Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
  • Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- returns all [cq:PageContent] nodes WHERE nodes exist under the "/content/we-retail"
-- AND node's navRoot equals to "true"
SELECT * FROM [cq:PageContent] as nodes WHERE ISDESCENDANTNODE ([/content/we-retail])
AND nodes.[navRoot] = CAST("true" AS BOOLEAN)

-- returns all [cq:PageContent] nodes WHERE nodes exist under the "/content/we-retail"
-- AND node's creation date is greater than "April 1st, 2018"
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content/we-retail])
AND nodes.[jcr:created] > CAST("2018-04-01T00:00:00.000Z" AS DATE)

-- returns all [cq:PageContent] nodes WHERE nodes exist under the "/content/we-retail"
-- AND node's maxRating is less than "5.0"
SELECT * FROM [cq:PageContent] as nodes WHERE ISDESCENDANTNODE ([/content/we-retail])
AND nodes.[maxRating] < CAST("5.0" AS DECIMAL)

Final Notes: When is the perfect time to use JCR-SQL2?

  1. Build a images and documents search component, if your AEM site that allows consumers to search for images or documents.
  2. Build a blog post search component, If your AEM site has a blog section.
  3. Build a secure resources query API, If you are allowing 3rd party applications to query and find nodes in your AEM site.

There’s many other things to utilize JCR-SQL2 to query nodes, but those are just some quick examples.

9. JCR-SQL2 Code Snippets Cheat Sheet

Use these code snippets below to rapidly build your AEM JCR SQL2 search queries, I will try my best to give you some really great examples:

a. Find all [nt:unstructured] nodes, under the path “/content/we-retail”, where the node name = “root”
1
2
3
SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/content/we-retail")
AND NAME() = "root"
b. Find all components ([nt:unstructured] nodes), under the path “/content/we-retail”, where component resourceType = “weretail/components/content/image”
1
2
3
SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/content/we-retail")
AND [sling:resourceType] = "weretail/components/content/image"
b. Find all components ([nt:unstructured] nodes), under the path “/content/we-retail”, where component resourceType = “weretail/components/content/image” AND the fileReference property contains with “/content/dam/we-retail/en/experiences”
1
2
3
4
SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/content/we-retail")
AND [sling:resourceType] = "weretail/components/content/image"
AND CONTAINS([fileReference], "/content/dam/we-retail/en/experiences")
c. Find all [nt:unstructured] nodes, under the path “/content/we-retail”, where the property(“jcr:title”) contains “Featured products”
1
2
3
SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/content/we-retail")
AND CONTAINS([jcr:title], "Featured products")
d. Find all pages (cq:Page nodes), under the path “/content/we-retail”, where the cq:lastModified date is before or equals to “January 1st, 2000”
1
2
3
4
SELECT page.* FROM [cq:Page] AS page
INNER JOIN [cq:PageContent] AS jcrContentNode ON ISCHILDNODE(jcrContentNode, page)
WHERE ISDESCENDANTNODE(page, "/content/we-retail")
AND jcrContentNode.[cq:lastModified] <= CAST("2023-01-01T00:00:00.000+00:00" AS DATE)
e. Find all pages (cq:Page nodes), under the path “/content/we-retail”, where the jcr:created date is before to “July 1st, 2022”
1
2
3
4
SELECT page.* FROM [cq:Page] AS page
INNER JOIN [cq:PageContent] AS jcrContentNode ON ISCHILDNODE(jcrContentNode, page)
WHERE ISDESCENDANTNODE(page, "/content/we-retail")
AND jcrContentNode.[jcr:created] < CAST("2022-07-01T00:00:00.000+00:00" AS DATE)
f. Find all [cq:PageContent] nodes, under the path “/content/we-retail”, where the cq:template equals to “/conf/we-retail/settings/wcm/templates/hero-page”

This AEM JCR SQ2 Query is an example for how to find all nodes with a specific template, results returned as cq:PageContent node.

1
2
3
SELECT * FROM [cq:PageContent] AS node
WHERE ISDESCENDANTNODE(node, "/content/we-retail")
AND [cq:template] = "/conf/we-retail/settings/wcm/templates/hero-page"
g. Find all pages (cq:Page nodes), under the path “/content/we-retail”, where the cq:template equals to “/conf/we-retail/settings/wcm/templates/hero-page”

This AEM JCR SQ2 Query is an example for how to find all nodes with a specific template, results returned as cq:Page node.

1
2
3
4
SELECT page.* FROM [cq:Page] AS page
INNER JOIN [cq:PageContent] AS jcrContentNode ON ISCHILDNODE(jcrContentNode, page)
WHERE ISDESCENDANTNODE(page, "/content/we-retail")
AND jcrContentNode.[cq:template] = "/conf/we-retail/settings/wcm/templates/hero-page"
h. Find all [cq:PageContent] nodes, under the path “/content/we-retail”, where the cq:tags[] contains “we-retail:gender/men”
1
2
3
4
This AEM JCR SQ2 Query is an example for how to find all nodes with a single cq:tags[], results returned as cq:PageContent node.
SELECT * FROM [cq:PageContent] AS node
WHERE ISDESCENDANTNODE(node, "/content/we-retail")
AND [cq:tags] = "we-retail:gender/men"
i. Find all [cq:PageContent] nodes, under the path “/content/we-retail”, where the cq:tags[] contains “we-retail:gender/men” AND cq:tags[] contains “we-retail:activity/surfing”
1
2
3
4
5
This AEM JCR SQ2 Query is an example for how to find all nodes with multiple cq:tags[], results returned as cq:PageContent node.
SELECT * FROM [cq:PageContent] AS node
WHERE ISDESCENDANTNODE(node, "/content/we-retail")
AND [cq:tags] = "we-retail:gender/men"
AND [cq:tags] = "we-retail:activity/surfing"







9. JCR-SQL2 Java Code Example Servlet (2023)

This is an example of how an AEM servlet would actually utilize the JCR-SQL2 Query with the examples above. It will output a JSON array of the page paths.

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
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;

import javax.jcr.Node;
import javax.jcr.NodeIterator;
import javax.jcr.RepositoryException;
import javax.jcr.Session;
import javax.jcr.query.Query;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;

import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.servlets.SlingSafeMethodsServlet;
import org.osgi.service.component.annotations.Component;

import com.google.gson.JsonArray;
import com.google.gson.JsonObject;

@Component(service = javax.servlet.Servlet.class, property = { "sling.servlet.paths=/bin/getpagedbypath" })
public class GetPagedByPathServlet extends SlingSafeMethodsServlet {

    @Override
    protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response)
            throws IOException {
        try {
            // Get the JCR session
            Session session = request.getResourceResolver().adaptTo(Session.class);

            // Query statement
            String queryString = "SELECT page.* FROM [cq:Page] AS page "
                    + "INNER JOIN [cq:PageContent] AS jcrContentNode ON ISCHILDNODE(jcrContentNode, page) "
                    + "WHERE ISDESCENDANTNODE(page, '/content/we-retail') "
                    + "AND jcrContentNode.[cq:lastModified] <= CAST('2023-01-01T00:00:00.000+00:00' AS DATE)";

            // Create the query object
            QueryManager queryManager = session.getWorkspace().getQueryManager();
            Query query = queryManager.createQuery(queryString, Query.JCR_SQL2);

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

            // Get the nodes from the query result
            NodeIterator nodeIterator = result.getNodes();

            // Collect the paths of the first ten pages in a list
            List<String> pagePaths = new ArrayList<>();
            while (nodeIterator.hasNext()) {
                Node pageNode = nodeIterator.nextNode();
                String pagePath = pageNode.getPath();
                pagePaths.add(pagePath);
            }

            // Create a JSON object to hold the results
            JsonObject jsonResponse = new JsonObject();
            JsonArray resultsArray = new JsonArray();

            // Add the paths to the JSON array
            for (String path : pagePaths) {
                resultsArray.add(path);
            }

            // Add the JSON array to the response object
            jsonResponse.add("results", resultsArray);

            // Set the response content type
            response.setContentType("application/json");

            // Write the JSON response to the output
            PrintWriter writer = response.getWriter();
            writer.print(jsonResponse.toString());
            writer.flush();
        } catch (RepositoryException e) {
            // Handle exception
            response.setStatus(SlingHttpServletResponse.SC_INTERNAL_SERVER_ERROR);
            response.getWriter().println("Error executing query: " + e.getMessage());
        }
    }
}

JSON response

1
2
3
4
5
6
7
8
9
10
11
12
13
14
{
   "results":[
      "/content/we-retail/page1",
      "/content/we-retail/page2",
      "/content/we-retail/page3",
      "/content/we-retail/page4",
      "/content/we-retail/page5",
      "/content/we-retail/page6",
      "/content/we-retail/page7",
      "/content/we-retail/page8",
      "/content/we-retail/page9",
      "/content/we-retail/page10"
   ]
}

Curl call

1
curl -X GET http://localhost:4502/bin/getpagedbypath

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?

Hello, I am an enthusiastic Adobe Community Advisor and a seasoned Lead AEM Developer. I am currently serving as an AEM Technical Lead at MNPDigital.ca, bringing over a decade of extensive web engineering experience and more than eight years of practical AEM experience to the table. My goal is to give back to the AEM Full Stack Development community by sharing my wealth of knowledge with others. You can connect with me on LinkedIn.
Website https://www.linkedin.com/in/briankasingli

24 thoughts on “How to use AEM JCR SQL2 query strings to query for nodes in Java Content Repository”

  2. Hi,
    Can you please let me know how can i get the count of results? i need only the count like in mysql ‘SELECT COUNT(*)….’

    Reply

  5. Its not my first time to go to see this website, i am visiting this website daily and take good data from here every day.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

Back To Top