aem apache-oak apache-sling java

JCR-SQL2 Query with Examples

Evgeniy Fitsner Software Engineer

· Dec 03, 2016 · 28 min read

Introduction

The Java Content Repository (JCR) specification defines two query languages: JCR-JQOM (a programmatic object model) and JCR-SQL2 (a SQL-like string syntax). In practice, almost everyone uses JCR-SQL2 because it is easier to write, read, and debug.

Apache Jackrabbit Oak (the repository engine behind AEM 6.x and Sling) supports JCR-SQL2 as a first-class query language. The older SQL and XPath dialects are still parsed for backward compatibility but are deprecated and may produce different results. If you are migrating from an older AEM or Jackrabbit installation, switch to JCR-SQL2.

Key differences from standard SQL:

• Node types replace tables. Write them in brackets: [cq:Page], [nt:unstructured].
• Selector aliases are mandatory in practice. Always assign one with AS.
• Path functions (ISDESCENDANTNODE, ISCHILDNODE, ISSAMENODE) navigate the tree hierarchy.
• Full-text search uses CONTAINS() rather than LIKE.
• Oak enforces stricter syntax than Jackrabbit 2. Queries that worked under Jackrabbit 2 may fail or return different results under Oak.

Throughout this guide, all examples use the AEM repository conventions (cq:Page, cq:PageContent, dam:Asset, and so on).

Syntax Fundamentals

Every JCR-SQL2 query follows this structure:

SELECT <selectors>
FROM <node-type> [AS <alias>]
[JOIN <node-type> [AS <alias>] ON <join-condition>]
[WHERE <predicate>]
[ORDER BY <property> [ASC|DESC]]
[LIMIT <count>]

Important syntax rules:

• Node types and property names that contain a colon or other special characters must be enclosed in brackets or double quotes: [cq:Page], [jcr:content], p.[jcr:content/cq:template].
• Aliases are optional in the spec but strongly recommended for readability.
• The WHERE clause is optional. Without it, the query returns every node of the specified type in the entire repository.
• LIMIT is not part of the JCR 2.0 specification but is supported by Oak as an extension. Use it to cap result sets and avoid traversal limits.

Minimal query:

SELECT * FROM [cq:Page] AS p

Full example with all clauses:

SELECT p.[jcr:path], p.[jcr:score]
FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])
  AND p.[jcr:content/jcr:lastModifiedBy] = 'admin'
ORDER BY p.[jcr:content/jcr:lastModified] DESC
LIMIT 10

Execution Methods

JCR API

Session session = ...;
Workspace workspace = session.getWorkspace();
QueryManager queryManager = workspace.getQueryManager();

String statement = "SELECT p.* FROM [cq:Page] AS p WHERE ISDESCENDANTNODE(p, [/content])";
Query query = queryManager.createQuery(statement, Query.JCR_SQL2);
QueryResult result = query.execute();

for (Row row : JcrUtils.getRows(result)) {
    String path = row.getValue("p.jcr:path").getString();
    // process each result row
}

To paginate results, set offset and limit on the Query object before executing:

query.setOffset(20);
query.setLimit(10);
QueryResult result = query.execute();

Sling API

The Sling ResourceResolver provides a shorthand that returns Resource objects directly:

ResourceResolver resolver = ...;

Iterator<Resource> results = resolver.findResources(
    "SELECT p.* FROM [cq:Page] AS p WHERE ISDESCENDANTNODE(p, [/content])",
    Query.JCR_SQL2
);

while (results.hasNext()) {
    Resource resource = results.next();
    String path = resource.getPath();
    // process each resource
}

For queries that need to map results to a specific resource type:

Iterator<Resource> pages = resolver.findResources(statement, Query.JCR_SQL2);
pages.forEachRemaining(r -> {
    if (r.isResourceType("cq/Page")) {
        Page page = r.adaptTo(Page.class);
        // work with the Page object
    }
});

Security note: User-supplied values in query strings must be escaped to prevent injection. Use SQL2Parser.escapeStringLiteral() from org.apache.jackrabbit.oak.query.SQL2Parser:

import org.apache.jackrabbit.oak.query.SQL2Parser;

String safeValue = SQL2Parser.escapeStringLiteral(userInput);
String statement = "SELECT p.* FROM [cq:Page] AS p WHERE p.[jcr:content/jcr:title] = '" + safeValue + "'";

Maven dependency:

<dependency>
    <groupId>org.apache.jackrabbit</groupId>
    <artifactId>oak-core</artifactId>
</dependency>

Common Node Types

Choosing the right node type narrows the result set and helps the query engine select an efficient index.

Node type	Description	When to use
cq:Page	AEM page node	Find pages under /content
cq:PageContent	AEM page content node (jcr:content)	Filter by page properties stored on jcr:content
nt:unstructured	Generic unstructured node	When no specific type exists; poor for index selection
oak:Unstructured	Oak-specific unstructured, no ordering	Same as nt:unstructured but without ordering overhead
dam:Asset	DAM asset metadata node	Find assets in /content/dam
sling:Folder	Sling folder (unordered)	Find folders without ordering cost
sling:OrderedFolder	Sling folder (ordered)	When you need ORDER BY sling:order
Mixin types (cq:Taggable, cq:Owner, etc.)	Optional type traits applied on top of primary type	Use in JOINs to filter by capability

Tip: Prefer oak:Unstructured over nt:unstructured and sling:Folder over sling:OrderedFolder when you do not need ordering. This avoids the overhead of maintaining order properties.

Comparison Operators

JCR-SQL2 supports standard comparison operators plus null checks:

-- Equality
SELECT p.* FROM [cq:Page] AS p
WHERE p.[jcr:content/author] = 'Henry Ford'

-- Inequality
SELECT p.* FROM [cq:Page] AS p
WHERE p.[jcr:content/author] <> 'Henry Ford'

-- Less than / greater than (works with dates, numbers, strings)
SELECT p.* FROM [cq:Page] AS p
WHERE p.[jcr:content/jcr:lastModified] > CAST('2024-01-01T00:00:00.000Z' AS DATE)

SELECT p.* FROM [cq:Page] AS p
WHERE p.[jcr:content/priority] >= CAST('5' AS LONG)

-- Null checks
SELECT p.* FROM [cq:Page] AS p
WHERE p.[jcr:content/cq:template] IS NOT NULL

SELECT p.* FROM [cq:Page] AS p
WHERE p.[jcr:content/cq:template] IS NULL

Note: Property values in JCR are typed. A LONG property compared with a string literal will not match unless you CAST the literal. Always match types explicitly to avoid silent mismatches.

Path Functions

Path functions navigate the hierarchical structure of the JCR repository.

ISDESCENDANTNODE

Returns nodes that are descendants of the given path (excluding the path node itself):

SELECT p.* FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])

Use this for subtree searches. It is one of the most common and efficient filters when combined with an index on jcr:path.

ISCHILDNODE

Returns nodes that are direct children of the given path:

SELECT p.* FROM [cq:Page] AS p
WHERE ISCHILDNODE(p, [/content/geometrixx/en])

Unlike ISDESCENDANTNODE, this only matches one level deep. Use it when you need immediate children rather than the entire subtree.

ISSAMENODE

Returns nodes at the specified path (used mainly in JOIN conditions):

SELECT p.* FROM [cq:Page] AS p
WHERE ISSAMENODE(p, [/content/geometrixx/en/company])

NAME and LOCALNAME

NAME() returns the full node name (including namespace prefix). LOCALNAME() returns the name without the namespace prefix:

SELECT p.* FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])
AND NAME(p) = 'geometrixx_user_grou'

SELECT n.* FROM [nt:base] AS n
WHERE LOCALNAME(n) = 'userprofile'

Use LOCALNAME() when the namespace prefix is variable but the local name is known.

PATH

PATH() returns the absolute path of a node. Useful in SELECT to retrieve paths alongside other columns:

SELECT PATH(p), p.[jcr:content/jcr:title] FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])

LIKE and Pattern Matching

The LIKE operator supports two wildcards:

Wildcard	Meaning
%	Zero or more characters
_	Exactly one character

-- Starts with
SELECT p.* FROM [cq:PageContent] AS p
WHERE p.[author] LIKE 'Henry Ford%'

-- Contains
SELECT p.* FROM [cq:PageContent] AS p
WHERE p.[author] LIKE '%Henry Ford%'

-- Single character wildcard
SELECT p.* FROM [cq:PageContent] AS p
WHERE p.[author] LIKE 'H_n_y Ford'

-- NOT LIKE: exclude patterns
SELECT p.* FROM [cq:PageContent] AS p
WHERE p.[author] NOT LIKE '%admin%'

Performance warning: LIKE '%…%' (leading wildcard) cannot use a standard property index and triggers a full traversal. Prefer CONTAINS() for full-text search.

Full-Text Search with CONTAINS

CONTAINS() uses the JCR full-text index (backed by Oak Lucene) and is significantly faster than LIKE '%term%' for large result sets.

-- Basic full-text search
SELECT p.* FROM [cq:PageContent] AS p
WHERE CONTAINS(p.[author], 'Henry Ford')

-- Wildcard prefix search (not middle or suffix)
SELECT p.* FROM [cq:PageContent] AS p
WHERE CONTAINS(p.[author], 'Hen*')

-- OR condition across multiple terms
SELECT p.* FROM [cq:PageContent] AS p
WHERE CONTAINS(p.[author], 'Henry OR Josephine OR "John Doe"')

-- Search across all properties of a node
SELECT p.* FROM [cq:Page] AS p
WHERE CONTAINS(p.*, 'workflow')

Syntax notes:

• Enclose phrases in double quotes inside the search term: "John Doe".
• Use OR (uppercase) to combine alternatives. AND is the default.
• Wildcards in CONTAINS() support prefix matching (Hen*) but not suffix or infix (*son or *enr*). Use Lucene-based indexes for those.
• {brace} syntax from older Jackrabbit is not needed in Oak. Plain terms work.

Search Excerpts with rep:excerpt

Retrieve highlighted text snippets around the matched search terms using rep:excerpt():

SELECT p.[jcr:path], p.[jcr:score], rep:excerpt(p.[jcr:content])
FROM [cq:Page] AS p
WHERE CONTAINS(p.*, 'workflow')

In Java, read the excerpt from the result row:

Row row = ...;
String excerpt = row.getValue("rep:excerpt(p.jcr:content)").getString();

Excerpts are limited to properties covered by the Lucene index definition. Ensure your index includes the properties you want highlighted.

CAST: Property Type Conversion

JCR-SQL2 can cast string literals to any JCR property type:

Type	Description
STRING	UTF-8 text (default)
BINARY	Binary stream
DATE	ISO 8601 date-time
LONG	64-bit integer
DOUBLE	IEEE 754 double
DECIMAL	Arbitrary precision decimal
BOOLEAN	true or false
NAME	JCR name (namespace-prefixed)
PATH	JCR path
REFERENCE	Strong reference by UUID
WEAKREFERENCE	Weak reference by UUID
URI	URI value

-- Date comparison
SELECT p.* FROM [cq:Page] AS p
WHERE p.[jcr:created] > CAST('2024-01-01T00:00:00.000Z' AS DATE)

-- Numeric comparison
SELECT p.* FROM [nt:unstructured] AS p
WHERE p.[priority] = CAST('5' AS LONG)

-- Boolean filter
SELECT p.* FROM [nt:unstructured] AS p
WHERE p.[isFeatured] = CAST('true' AS BOOLEAN)

Important: Property type mismatches are a common source of bugs. A LONG property filtered with a string literal (p.[priority] = '5') will return zero results. Always CAST the literal to match the stored type.

JOIN Operations

Apache Oak supports three join types: INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN.

INNER JOIN

Returns only rows where both selectors match. The most common join pattern in AEM joins a page with its jcr:content child:

SELECT parent.[jcr:path], child.[jcr:title]
FROM [cq:Page] AS parent
INNER JOIN [cq:PageContent] AS child
  ON ISCHILDNODE(child, parent)
WHERE ISDESCENDANTNODE(parent, [/content/geometrixx/en])
  AND child.[cq:template] = '/libs/social/blog/templates/page'

LEFT OUTER JOIN

Returns all rows from the left selector, plus matched rows from the right. If there is no match, right-side columns are NULL:

SELECT parent.[jcr:path], child.[jcr:title]
FROM [cq:Page] AS parent
LEFT OUTER JOIN [cq:PageContent] AS child
  ON ISCHILDNODE(child, parent)
WHERE ISDESCENDANTNODE(parent, [/content/geometrixx/en])

Useful for finding pages that may or may not have certain child nodes.

RIGHT OUTER JOIN for Corruption Detection

Returns all rows from the right selector. In AEM, this can find orphaned content nodes (content without a parent page):

SELECT parent.* FROM [cq:Page] AS parent
RIGHT OUTER JOIN [cq:PageContent] AS child
  ON ISCHILDNODE(child, parent)
WHERE ISDESCENDANTNODE(parent, [/content])

This identifies content nodes whose parent page is missing, which can indicate repository corruption or failed deployments.

Mixin Matching with ISSAMENODE

ISSAMENODE in a JOIN filters nodes that have a mixin type applied:

SELECT meta.* FROM [nt:base] AS meta
INNER JOIN [cq:Taggable] AS taggable
  ON ISSAMENODE(meta, taggable)
WHERE ISDESCENDANTNODE(meta, [/content/dam/geometrixx/banners])

This returns all taggable assets under the banners path.

ISCHILDNODE vs ISSAMENODE in JOINs:

Condition	Relationship	Use case
ISCHILDNODE(child, parent)	child is a direct child of parent	Join page to jcr:content
ISSAMENODE(a, b)	a and b refer to the same node	Apply mixin type filters

ORDER BY and LIMIT

Sorting Results

SELECT p.* FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])
ORDER BY p.[jcr:content/jcr:lastModified] DESC

For nested properties, use the slash-separated path syntax inside brackets. ASC is the default if you omit the direction.

Pagination

Combine ORDER BY with LIMIT and offset to paginate results. In the JCR API, use query.setLimit() and query.setOffset():

Query query = queryManager.createQuery(statement, Query.JCR_SQL2);
query.setOffset(0);   // first page
query.setLimit(20);    // 20 results per page
QueryResult result = query.execute();

Note: LIMIT and OFFSET are Oak extensions. The JCR 2.0 specification does not define SQL-level pagination; set these on the Query object instead. Oak does support LIMIT in the SQL string as a convenience.

UNION: Combining Results

The UNION operator combines results from multiple SELECT statements. Use it when a single query cannot express all conditions efficiently:

SELECT parent.* FROM [cq:Page] AS parent
RIGHT OUTER JOIN [cq:PageContent] AS child
  ON ISCHILDNODE(child, parent)
WHERE ISDESCENDANTNODE(parent, [/content/site1/en/blog])
UNION
SELECT parent.* FROM [cq:Page] AS parent
RIGHT OUTER JOIN [cq:PageContent] AS child
  ON ISCHILDNODE(child, parent)
WHERE ISDESCENDANTNODE(parent, [/content/site2/en/blog])

UNION removes duplicate rows. Use UNION ALL to keep duplicates if you need them.

When to use UNION:

• Searching across disjoint subtrees that cannot be expressed with a single ISDESCENDANTNODE or OR clause.
• Combining results from different node type hierarchies.
• When an OR condition causes Oak to avoid an index, splitting into UNION sub-queries can force index usage per branch.

XPath to JCR-SQL2 Migration

XPath was the primary query language in older AEM/CQ versions. Since AEM 6.0, JCR-SQL2 is preferred. Below is a quick-reference migration table:

Task	XPath	JCR-SQL2
Find by type	/jcr:root/content//*	SELECT * FROM [nt:base] AS n WHERE ISDESCENDANTNODE(n, [/content])
Find pages	/jcr:root/content//element(*, cq:Page)	SELECT * FROM [cq:Page] AS p WHERE ISDESCENDANTNODE(p, [/content])
Property equals	[@jcr:content/author='Henry']	WHERE p.[jcr:content/author] = 'Henry'
Property contains	[jcr:contains(@jcr:content, 'workflow')]	WHERE CONTAINS(p.[jcr:content], 'workflow')
Path restriction	/jcr:root/content/geometrixx/en//*	WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])
Order by	order by @jcr:score descending	ORDER BY p.[jcr:score] DESC
Limit	limit 10 (Jackrabbit extension)	LIMIT 10 (Oak extension)

Deprecated XPath warning: Oak translates XPath queries internally to JCR-SQL2 before execution. You can see the translated SQL2 by checking the query plan in the Query Debugger. For new development, write SQL2 directly to avoid translation surprises.

AEM QueryBuilder as an Alternative

AEM provides a higher-level query abstraction called QueryBuilder (aka QueryBuilder API). It generates JCR-SQL2 or XPath internally and is commonly used in component dialogs and servlets.

When to use QueryBuilder

• You need pagination, faceting, or hit highlighting built in.
• You are writing component logic where you want to pass parameters from a dialog.
• You want to leverage Oak indexes implicitly without writing raw SQL2.

When to use JCR-SQL2

• You need JOIN, UNION, or RIGHT OUTER JOIN.
• You need CAST operations or null checks.
• You are debugging performance and need full control over the query.
• You are writing one-off maintenance scripts or reports.

Side-by-side comparison

Find pages modified after a date:

QueryBuilder (in Java):

Map<String, String> predicates = new HashMap<>();
predicates.put("type", "cq:Page");
predicates.put("path", "/content/geometrixx/en");
predicates.put("daterange.property", "jcr:content/jcr:lastModified");
predicates.put("daterange.lowerBound", "2024-01-01T00:00:00.000Z");
predicates.put("orderby", "@jcr:content/jcr:lastModified");
predicates.put("orderby.sort", "desc");

QueryBuilder builder = resourceResolver.adaptTo(QueryBuilder.class);
Query query = builder.createQuery(PredicateGroup.create(predicates), resourceResolver.adaptTo(Session.class));
query.setHitsPerPage(20);
query.setStart(0);
SearchResult result = query.getResult();

JCR-SQL2 equivalent:

SELECT p.* FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])
  AND p.[jcr:content/jcr:lastModified] > CAST('2024-01-01T00:00:00.000Z' AS DATE)
ORDER BY p.[jcr:content/jcr:lastModified] DESC
LIMIT 20

Key differences:

Feature	QueryBuilder	JCR-SQL2
Pagination	Built-in (setHitsPerPage, setStart)	Manual (LIMIT / OFFSET or Query API)
Facets	Built-in	Not available
Excerpts	Built-in via rep:excerpt()	Available via rep:excerpt() in SELECT
JOINs	Not supported	Full INNER, LEFT/RIGHT OUTER
UNION	Not supported	Supported
NULL checks	property.operation = 'not' workaround	IS NULL / IS NOT NULL
Debugging	OSGi Query Debugger at /libs/cq/search/content/querydebug.html	Same tool, or CRXDE
Caching	Result cache via QueryBuilders	No caching

Recommendation: Use QueryBuilder for typical AEM component queries (list of pages, searches, facet navigation). Use JCR-SQL2 for complex joins, report scripts, and performance debugging.

Performance and Indexing

Oak does not scan every node for every query. It uses indexes to locate results efficiently. If no applicable index exists, Oak falls back to a traversal - reading every node in the subtree - which triggers a read limit exception:

java.lang.UnsupportedOperationException: The query read or traversed more than 100000 nodes.
To avoid affecting other tasks, processing was stopped.

See the OAK Query Aborted post for details on this error and workarounds.

Practical indexing tips

1. Always restrict the path. Add ISDESCENDANTNODE or ISCHILDNODE to every query. Without a path restriction, Oak may traverse the entire repository.

2. Create a property index for equality lookups. If you frequently filter by a single property (e.g., cq:template), create an Oak Property Index on that property:

   {
     "jcr:primaryType": "oak:QueryIndexDefinition",
     "type": "property",
     "propertyNames": ["cq:template"],
     "declaringNodeTypes": ["cq:PageContent"],
     "name": "cqTemplateIndex"
   }
   

3. Use Lucene indexes for full-text and multi-property queries. Property indexes do not support CONTAINS(). Create a Lucene index definition under /oak:index/lucene (or a custom one) that includes the properties you search against.

4. Avoid LIKE '%…%' on large sets. A leading wildcard forces a traversal. Use CONTAINS() instead.

5. Minimize JOINs. Each JOIN adds a selector and increases the number of nodes the engine must evaluate. If you can express the query with path notation (p.[jcr:content/author]) instead of a JOIN, prefer that.

6. Use EXPLAIN to verify index usage. Prefix your query with EXPLAIN to see the execution plan:

   EXPLAIN SELECT p.* FROM [cq:Page] AS p
   WHERE ISDESCENDANTNODE(p, [/content]) AND p.[jcr:content/cq:template] = '/conf/wknd/settings/wcm/templates/article-page'
   

   If the plan shows Traversal, the query is not using any index and will be slow or hit the read limit.

7. Set read limits in production. Configure LimitReads and LimitInMemory via OSGi or JMX (org.apache.jackrabbit.oak:name=settings,type=QueryEngineSettings). The defaults (100,000 nodes) prevent runaway queries from degrading system performance.

Debugging Queries

Query Debugger

AEM ships a web-based Query Debugger at:

http://localhost:4502/libs/cq/search/content/querydebug.html

Enter a JCR-SQL2 or XPath query and click Search. The debugger shows:

• Results with paths, scores, and excerpts
• The query execution plan (equivalent to EXPLAIN)
• Execution time

CRXDE Lite

In CRXDE Lite (http://localhost:4502/crx/de), use the Tools > Query panel to run JCR-SQL2 queries directly against the repository.

Slow Query Logging

Enable slow query logging in the OSGi console to identify problematic queries:

1. Go to http://localhost:4502/system/console/configMgr
2. Find Apache Sling Logging Configuration or Oak Query Engine Settings
3. Set the log level to DEBUG for org.apache.jackrabbit.oak.query

Alternatively, use the JMX bean org.apache.jackrabbit.oak:name=query,type=QueryEngineSettings to adjust thresholds and log slow queries at runtime without restarting.

Common Debugging Workflow

1. Write your query in JCR-SQL2.
2. Run EXPLAIN SELECT … and check if the plan is Traversal or uses an index.
3. If Traversal, add a path restriction (ISDESCENDANTNODE) and/or verify that an appropriate Oak index exists.
4. Run the query in the Query Debugger and check execution time.
5. If the query is still slow, consider restructuring it (flip JOIN order, split into UNION, or use QueryBuilder).

Contents