AEM Guide

Querying Content in AEM: JCR-SQL2, QueryBuilder, and Why You Need an Oak Index

How to choose between QueryBuilder and JCR-SQL2 when querying content in AEM, why a query without a matching Oak index becomes a traversal, and why that can fail in AEM as a Cloud Service even when it works locally.

jcroakquerybuilderjcr-sql2performance

There are two common ways to query content in an AEM project: the QueryBuilder API (the one behind the Content Finder, Asset Search, and most Granite UI search widgets) and hand-written JCR-SQL2 executed against a Session/ResourceResolver from Java code. Both end up in the same place: the Apache Jackrabbit Oak query engine. Understanding that — and understanding when Oak actually has an index to resolve your query and when it doesn’t — is what separates an author search that responds in milliseconds from a scheduled job that blows up in production.

Two ways to query content in AEM

QueryBuilder (com.day.cq.search.QueryBuilder) is AEM’s own API for building queries from declarative predicates instead of SQL syntax. It powers the Content Finder, Asset Search, and essentially every granite:search widget in a touch UI dialog:

import com.day.cq.search.PredicateGroup;
import com.day.cq.search.Query;
import com.day.cq.search.QueryBuilder;
import com.day.cq.search.result.Hit;
import com.day.cq.search.result.SearchResult;

Map<String, String> params = new HashMap<>();
params.put("path", "/content/we-retail");
params.put("type", "cq:Page");
params.put("property", "jcr:content/cq:template");
params.put("property.value", "/conf/we-retail/settings/wcm/templates/product-page");
params.put("p.limit", "20");

QueryBuilder queryBuilder = resourceResolver.adaptTo(QueryBuilder.class);
Query query = queryBuilder.createQuery(PredicateGroup.create(params), session);
SearchResult result = query.getResult();

for (Hit hit : result.getHits()) {
    Resource page = hit.getResource();
    // work with the resource
}

The alternative is writing JCR-SQL2 directly and executing it against the ResourceResolver (which internally delegates to a JCR Session). That’s the usual path in schedulers, workflows, listeners, and migration scripts, where building a PredicateGroup for a one-off query isn’t worth the ceremony:

import javax.jcr.query.Query;

String statement =
    "SELECT * FROM [cq:Page] AS page " +
    "WHERE ISDESCENDANTNODE(page, [/content/we-retail]) " +
    "AND [jcr:content/cq:template] = " +
    "'/conf/we-retail/settings/wcm/templates/product-page'";

Iterator<Resource> pages = resourceResolver.findResources(statement, Query.JCR_SQL2);

Neither approach “skips” the need for an index. QueryBuilder translates its predicates into a query that Oak processes exactly like a hand-written JCR-SQL2 statement — the difference is that predicates hide the resulting query structure, so it’s easier to combine predicates that, together, have no index covering them without noticing until the query is already running in production.

It’s also worth remembering that both Hit.getResource() and findResources() return Resource objects obtained through whichever ResourceResolver you used to run the query. If that resolver is one you opened yourself (for example via getServiceResourceResolver), the same rule still applies: always close it in a try-with-resources block.

Why the underlying index matters: Oak’s cost-based query engine

Oak doesn’t naively execute queries against the content tree. It uses a cost-based optimizer: it asks every available index how much it would cost to resolve the query (a number between 1 — a very cheap point lookup — and infinity if the index can’t help at all) and picks whichever index is cheapest.

The problem shows up when no index can resolve the query at all. In that case, Oak falls back to a traversal: it walks every node in the subtree named by the query, one at a time, evaluating the condition against each one instead of jumping straight to matching results. When this happens, Oak logs a warning:

Traversal query (query without index): {statement}; consider creating an index

A traversal isn’t a syntax error or a bug — it’s a perfectly valid query that Oak can still resolve, just at a cost that grows linearly with the number of nodes in the subtree. Against /content/we-retail on your local author instance, with a handful of sample pages, that can take a few milliseconds and go completely unnoticed during development.

Why this is especially dangerous on AEM as a Cloud Service

The same behavior that’s invisible locally becomes a real problem against production content volume. The more pages, assets, or nodes in the subtree you’re traversing, the more expensive the traversal — and that cost isn’t just “slower”: Oak enforces a default read limit. When a query reads or traverses more than 100,000 nodes, it stops and throws an exception:

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

There’s an equivalent limit for in-memory result sorting (when an ORDER BY can’t be resolved through an index), with a similar message once more than 500,000 nodes have been read into memory. These limits have been enabled by default since AEM 6.3, precisely to stop a poorly indexed query from consuming repository resources without bound and affecting other concurrent work (other queries, replication, async indexing).

That has a very concrete consequence for an AEM developer: a query that “works” against a local author instance with sample content can fail outright — not just run slowly — the moment it’s deployed against a production site’s real content volume. And on AEM as a Cloud Service, where you don’t have system-level access to just raise that threshold as a quick fix, the supported answer isn’t touching the limit — it’s indexing or scoping the query. AEM as a Cloud Service’s own query and indexing best-practice guidance is explicit that every query should be explained before it ships and should not show a traversal in its execution plan.

Checking whether your query uses an index

AEM exposes a Query Performance tool (also known as “Explain Query”) inside the Operations dashboard, at /libs/granite/operations/content/diagnosistools/queryPerformance.html. On AEM as a Cloud Service, the equivalent view is reached through Cloud Manager’s Developer Console. There you can paste XPath, JCR-SQL2, or the statement a QueryBuilder call produced and get the real execution plan:

The tool also scores queries by “Read Optimization” (the ratio between scanned nodes and nodes that actually match the result): a well-indexed query typically scores around 90% or higher; a low score means that even though the query technically uses an index, it’s still reading far more nodes than necessary — for instance because the ORDER BY isn’t covered by the index and Oak has to sort in memory.

For queries built with QueryBuilder specifically, on a local development environment you can use the QueryBuilder debug console at /libs/cq/search/content/querydebug.html, which lets you run a set of predicates and see both the results and the resulting plan before writing that same PredicateGroup into Java code.

When a custom Oak index is actually warranted

Not every slow query needs a new index. AEM already ships Lucene indexes for the most common cases — pages (cqPageLucene), assets (damAssetLucene), tags, node type — covering most lookups by sling:resourceType, template, jcr:primaryType, or tag paths. Before creating your own index, use the Explain Query tool to check whether one of the existing indexes already covers the combination of restrictions you need: a redundant index doesn’t improve anything and adds indexing overhead on every write to the repository.

A custom index is warranted when you’re filtering or sorting on project-specific properties that no out-of-the-box index covers — for example, a combination of custom DAM metadata, or an ORDER BY on a business property that isn’t part of the standard indexes. In an AEM project, the index definition is deployed as immutable content inside a package (typically ui.apps or a dedicated index module), targeting the /oak:index/<name> node:

<!-- jcr_root/_oak_index/myprojectAssetLucene/.content.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:jcr="http://www.jcp.org/jcr/1.0"
    jcr:primaryType="oak:QueryIndexDefinition"
    type="lucene"
    async="async"
    compatVersion="{Long}2"
    includedPaths="[/content/dam]">
    <indexRules jcr:primaryType="nt:unstructured">
        <dam:Asset jcr:primaryType="nt:unstructured">
            <properties jcr:primaryType="nt:unstructured">
                <projectCategory
                    jcr:primaryType="nt:unstructured"
                    name="jcr:content/metadata/projectCategory"
                    propertyIndex="{Boolean}true"/>
            </properties>
        </dam:Asset>
    </indexRules>
</jcr:root>

AEM as a Cloud Service has no production CRXDE Lite to hand-edit an index: the definition travels with your code, goes through the Cloud Manager pipeline, and should be validated against a staging environment with representative content volume before reaching production — precisely because a query’s behavior against 200 sample pages and against 200,000 real pages can be radically different.

Where this comes up in a real AEM project