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.


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


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.

Certified AEM Developer who has been working on AEM software developer for the past 5 years.

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

  1. 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(*)….’

Leave a Reply

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

Back To Top