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 a 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.

JCR-SQL2 Syntax

1. The JCR-SQL2 SELECT Statement

• The SELECT statement is used to select all JCR nodes that match 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.

-- 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

• Select 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.

-- 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

• Select 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.

-- 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

• Select 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.

-- 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.

-- 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.

-- 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.

-- 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

1. Convert an expression from one data type to another.
2. 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.

-- 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)

Note: 

When is the perfect time to use JCR-SQL2?

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

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