Querying: QueryBuilder, JCR-SQL2 and Oak Indexes#

#21 – Querying: QueryBuilder, JCR-SQL2 and Oak Indexes

Target: 3–4 years experienced AEM Developer Covers from your additional list: Query Builder · JCR-SQL2 · Lucene indexes · Oak query performance Project domain: a global energy technology company's marketing site.

#Before we start — the one fact this whole topic hangs on

There is a single sentence that, if you genuinely understand it, gives you most of this topic:

In Oak, a query that has no matching index does not fail. It traverses.

Not an error. Not a warning to the caller. Oak simply starts walking the content tree, node by node, checking each one against your criteria. On a repository with a few hundred nodes that's instant. On a real production repository with millions, it can take the instance down.

And here's what makes it genuinely dangerous rather than merely slow: it works perfectly on your laptop. Your local instance has a fraction of the content, so a traversing query returns in milliseconds and every test passes. The failure only appears when the content volume is real — which is stage, or worse, production.

That asymmetry is why this topic matters disproportionately, and it's why the interview question is never really "how do you write a query." It's:

"How do you know your query is safe?"

Section 2.7 is that answer. Everything before it is the machinery you need to give it.

#1. Introduction

#1.1 Why you query at all

Most of what a component does is read content it already knows the path of. A Sling Model reads its own node's properties (file 05); a page renders its own children.

Querying is for the cases where you don't know the paths in advance:

• A product listing that shows every product category tagged "transformers"
• A search results page
• A report of all pages modified in the last month
• Finding every page that references a particular Content Fragment

And the important framing: each of those is a case where you're asking the repository a question rather than reading a known location. That's fundamentally more expensive, and it's why "can I avoid querying entirely?" is the first question worth asking.

#1.2 The three ways to query

AEM gives you three, and they're related rather than alternatives:

The key relationship, and it's a common interview question:

QueryBuilder compiles down to JCR-SQL2. It is not a different engine, and it is not faster or slower. It's a more convenient way to produce the same thing.

So choosing between them is about readability and how the query is constructed, not performance.

#1.3 A real project example to adapt

"We query in a few places — the product category listing, site search, and a scheduled report. The rule we settled on is that a component should prefer walking a known path over running a query at all: if the content is all under one parent, listChildren with a hard ceiling is predictable and needs no index.

Where we genuinely do query — search, and tag-based listings — the query is scoped to a path, always has a limit, and we've confirmed it resolves against an index rather than traversing. We check that with the Query Performance tool rather than assuming, because a query that traverses is fine on a laptop and dangerous on production.

We learned that the hard way: a listing component ran a repository-wide tag query that was instant locally and crawled on stage, because stage had the real content volume."

That covers the preference for traversal-over-query, the discipline when you must query, the verification step, and the incident that taught it.

#2. Core Concepts

#2.1 QueryBuilder — the API you'll actually use

QueryBuilder takes a map of predicates and turns them into a query. It's the most common approach on real projects because the predicate map is easy to read and easy to build dynamically.

Map<String, String> predicates = new HashMap<>();
predicates.put("path", "/content/energy/global/en/products");
predicates.put("type", "cq:Page");
predicates.put("property", "jcr:content/productLine");
predicates.put("property.value", "transformers");
predicates.put("p.limit", "20");

Query query = queryBuilder.createQuery(PredicateGroup.create(predicates), session);
SearchResult result = query.getResult();

Reading that: find cq:Page nodes under the products path whose jcr:content has a productLine property equal to transformers, and give me at most twenty.

#2.2 The predicates worth knowing

You won't memorise all of them, and you don't need to. These are the ones that appear in real code:

And the ones that control results rather than matching:

p.limit deserves emphasis, because its default behaviour surprises people and the wrong value is genuinely dangerous — section 2.5.

#2.3 Property paths in predicates

A detail that trips people up on AEM specifically.

Remember from file 20 that a page's content lives on its jcr:content child, not the page node. So when you're matching a page by one of its properties, the property path is relative to the page node and has to include jcr:content:

predicates.put("type", "cq:Page");
predicates.put("property", "jcr:content/productLine");   // NOT just "productLine"

Get that wrong and the query returns nothing, with no error — because the property genuinely doesn't exist at the path you asked for. It's a silent empty result, which is the hardest kind to debug.

#2.4 Grouping and OR logic

Predicates are ANDed together by default. For OR, you use a group:

predicates.put("group.p.or", "true");
predicates.put("group.1_property", "jcr:content/productLine");
predicates.put("group.1_property.value", "transformers");
predicates.put("group.2_property", "jcr:content/productLine");
predicates.put("group.2_property.value", "switchgear");

The numbered prefixes are how QueryBuilder distinguishes predicates within a group. It's verbose, and it's one of the reasons people sometimes prefer writing JCR-SQL2 directly for a complex query.

#2.5 p.limit — and why -1 is dangerous

p.limit=-1 means "return everything."

That sounds convenient and it is occasionally correct — a one-off administrative report run by hand, for example. In a component, in a servlet, or in anything on the request path, it is a production incident waiting to happen.

Why: you don't control how much content exists. A query that returns forty results today returns four thousand after a content migration, and the code that built a list from it now builds a list of four thousand — with whatever per-item work it does multiplied accordingly.

This is the same principle as file 05's hard ceiling, arriving from the query side. And the right pattern uses both:

predicates.put("p.limit", "20");        // bound the QUERY

// and in the model, bound the LIST as well
if (results.size() > MAX_ITEMS) { ... }

Belt and braces, because the query limit protects the repository and the code ceiling protects your rendering.

p.guessTotal is worth knowing alongside it. By default, getting the total number of matches means counting them all — which for a broad query means Oak evaluating far more than the page you're showing. p.guessTotal tells it to estimate instead, which is usually all a "showing 20 of about 500" label needs.

#2.6 JCR-SQL2

The underlying language. QueryBuilder generates it; you can also write it directly.

SELECT * FROM [cq:Page] AS page
WHERE ISDESCENDANTNODE(page, '/content/energy/global/en/products')
  AND page.[jcr:content/productLine] = 'transformers'
ORDER BY page.[jcr:content/jcr:title]

The constructs worth recognising:

When to write SQL2 directly rather than using QueryBuilder: when the query is complex enough that the predicate map becomes harder to read than the SQL — joins, or nested OR logic. For most component queries, QueryBuilder is more readable.

And XPath is the older JCR query language. You'll see it in legacy code and in some Oak tooling output. Worth recognising; not worth writing new.

#2.7 Indexes — the part that actually matters

Now the central mechanism.

Oak stores content as a tree. To answer "find all pages where productLine is transformers ", it needs either an index that maps property values to nodes, or it has to look at every node.

There is no third option, and Oak will happily do the second one.

Where indexes live: under /oak:index in the repository. They're content, which means they can be deployed in a package like anything else (file 25).

The two kinds you need to distinguish:

A property index maps values of a specific property to the nodes holding it. Good for exact matches on a property you query often. Updated synchronously as content changes.

A Lucene index is a full-text search index. It handles text search, and it can also handle property queries. It's updated asynchronously, which has a consequence worth knowing — section 2.9.

How an index definition is shaped, at the level worth knowing rather than memorising:

An index is a node under /oak:index of type oak:QueryIndexDefinition, carrying a type property that says which kind it is — property or lucene — and then configuration describing what it covers. A property index names the properties it indexes; a Lucene index carries rules describing which node types and properties it covers, and is marked asynchronous.

Setting reindex to true triggers a rebuild, which is how a newly deployed index gets populated with existing content.

I'd be careful about reciting a full index definition from memory in an interview. The structure has evolved across Oak versions, and getting a property name wrong is worse than saying "I'd take the existing definition as a starting point and adjust it." What matters is that you know indexes exist under /oak:index, that there are property and Lucene kinds, that they must be deployed and reindexed, and that a query without one traverses.

#2.8 Custom indexes on AEM as a Cloud Service

On Cloud Service, custom index definitions ship in ui.apps as immutable content (file 25) — they're code, not content you edit at runtime.

There's a naming convention: a custom index carries a suffix marking it as yours, conventionally something like -custom-1. The reason is that Adobe ships and updates its own index definitions, and the suffix keeps yours from colliding with or being overwritten by theirs.

And the operational consequence worth stating: deploying an index definition doesn't instantly make queries fast. The index has to be built, which on a large repository takes real time. So an index deployment is an operation with a lag, not an instant switch.

#2.9 Asynchronous indexing and its consequence

Lucene indexes are updated asynchronously — content is written first, and the index catches up shortly after.

Which means there's a window where content exists and isn't yet findable by a query. Usually seconds, but it's real.

Where this bites: code that writes content and then immediately queries for it. That pattern looks correct and fails intermittently, because sometimes the index has caught up and sometimes it hasn't.

The fix is usually to not do that — if you just wrote it, you know its path, so read it directly rather than searching for it. That's the general principle of this file expressed at small scale.

#2.10 The rule that prevents most problems

Here is the practical guidance that matters more than any index detail:

Prefer walking a known path over running a query.

If the content you need is all under one parent — product categories under a products page, articles under a news section — then listChildren on that parent is:

• Predictable — the cost is proportional to the number of children, which you can bound
• Index-free — no index needed, so nothing to deploy, maintain or get wrong
• Immune to the laptop/production gap — it behaves the same everywhere

This is exactly what file 02's listing component and file 05's CategoryListingModel do, and it's why they do it.

Query when you genuinely can't walk, which is when the content could be anywhere and is selected by criteria rather than location — search, or a tag-based listing spanning the site.

And when you do query, three rules:

1. Scope it to a path — this bounds the work even when an index isn't perfect
2. Always set a limit — never -1 on the request path
3. Verify it uses an index — with the tooling, not by assumption

#2.11 Permissions and query results

A detail that has both a correctness and a performance dimension.

Query results are filtered by the permissions of the user running the query (file 13). So the same query returns different results for an author and for anonymous on publish.

The correctness consequence: a component that queries will show fewer results on publish than on author if some content isn't readable by anonymous. That's correct behaviour, and it's another variant of the "works on author, blank on publish" family.

The performance consequence: Oak may have to evaluate more candidate nodes than it returns, because it fetches matches and then filters by permission. On a heavily-restricted tree, a query that looks like it returns ten results may have evaluated far more.

And the practical note: if you're querying with a service user (file 13) to read past the visitor's permissions, you're also bypassing that filtering — so you have to be deliberate about not exposing content the visitor shouldn't see.

#3. Internal Working

#3.1 How Oak answers a query

graph TD A["A query arrives"] --> B["Oak's query engine parses it<br/>(QueryBuilder has already<br/>compiled to JCR-SQL2)"] B --> C["Which index can serve this?"] C --> D{"Is there a matching index?"} D -->|"Property index"| E["Look up values directly"] D -->|"Lucene index"| F["Search the index"] D -->|"NONE"| G["TRAVERSE<br/>walk the tree node by node"] E --> H["Candidate nodes"] F --> H G --> H H --> I["Filter by the querying<br/>user's PERMISSIONS"] I --> J["Apply limit / offset"] J --> K["Results"]

Three things to draw out:

The engine chooses the index, and it may choose one you didn't expect — or none. That's why you verify rather than assume.

Traversal is a normal path through this diagram, not an error branch. Nothing fails; it just does the slow thing.

Permission filtering happens after matching, which is why a restricted tree costs more than the result count suggests.

#3.2 Why it works locally and fails on stage

graph LR A["Same query"] --> B["LOCAL<br/>~2,000 nodes<br/>traversal: milliseconds<br/>every test passes ✓"] A --> C["STAGE / PROD<br/>millions of nodes<br/>traversal: seconds or worse<br/>threads consumed"]

This is the whole reason the topic is dangerous. A traversing query is not detectably wrong in development. There's no exception, no failing test, no code review signal — the only difference is content volume, and your laptop doesn't have it.

Which is why the verification step is not optional, and why "I checked it uses an index" is a much better answer than "it worked when I tested it."

#3.3 Reindexing

When you deploy a new index definition, it exists but is empty. It has to be built by reading through the existing content.

graph TD A["Index definition deployed"] --> B["Marked for reindex"] B --> C["Oak reads through existing content<br/>— takes real time on a large repo"] C --> D["Index populated"] D --> E["Queries now resolve against it"]

The operational point: between deployment and completion, queries that should use the index don't. So an index deployment on a large repository is a planned operation, not an instant fix — and it's worth doing when the instance has capacity.

And after a content migration, indexes may need rebuilding, which is why "search returns nothing after a migration" is a standard scenario (file 20).

#8. Coding Examples

#8.1 A safe QueryBuilder query

package com.energy.core.services.impl;

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;
import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.jcr.Session;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component(service = ProductSearchService.class)
public class ProductSearchServiceImpl implements ProductSearchService {

    private static final Logger LOG =
            LoggerFactory.getLogger(ProductSearchServiceImpl.class);

    /**
     * A HARD CEILING, independent of whatever limit is requested.
     *
     * The query limit protects the repository; this protects the
     * rendering if someone passes a large limit or the content
     * volume grows. Belt and braces -- the same principle as
     * file 05's bounded traversal.
     */
    private static final int MAX_RESULTS = 100;

    @Reference
    private QueryBuilder queryBuilder;

    @Override
    public List<String> findProducts(ResourceResolver resolver,
                                     String productLine,
                                     int limit) {

        Session session = resolver.adaptTo(Session.class);
        if (session == null) {
            return new ArrayList<>();
        }

        int effectiveLimit = Math.min(Math.max(limit, 1), MAX_RESULTS);

        Map<String, String> predicates = new HashMap<>();

        // SCOPE TO A PATH.
        //
        // This is the single most important predicate for performance.
        // It bounds the work even when the index isn't a perfect match,
        // and it stops a query that should look at products from
        // walking the entire repository.
        predicates.put("path", "/content/energy/global/en/products");

        predicates.put("type", "cq:Page");

        // NOTE THE jcr:content/ PREFIX.
        //
        // A page's properties live on its jcr:content child (file 20),
        // NOT on the page node. Writing "productLine" here asks for a
        // property that genuinely doesn't exist at that path -- and you
        // get an EMPTY RESULT with no error, which looks like a content
        // problem rather than a code bug.
        predicates.put("property", "jcr:content/productLine");
        predicates.put("property.value", productLine);

        // ALWAYS a real limit. NEVER p.limit=-1 on the request path --
        // you don't control how much content exists next year.
        predicates.put("p.limit", String.valueOf(effectiveLimit));

        // Estimate the total rather than counting every match. An exact
        // count means evaluating far more than the page being shown.
        predicates.put("p.guessTotal", "true");

        try {
            Query query = queryBuilder.createQuery(
                    PredicateGroup.create(predicates), session);

            SearchResult result = query.getResult();

            List<String> paths = new ArrayList<>();
            for (Hit hit : result.getHits()) {
                if (paths.size() >= effectiveLimit) {
                    break;                      // defensive, on top of p.limit
                }
                paths.add(hit.getPath());
            }
            return paths;

        } catch (Exception e) {
            // NEVER propagate out of something used during rendering --
            // degrade the component, don't break the page (file 06).
            LOG.warn("Product search failed for productLine={}", productLine, e);
            return new ArrayList<>();
        }
    }
}

The five decisions to be able to defend:

path first — it bounds the work regardless of index quality.

jcr:content/ in the property path — the silent-empty-result trap.

A real limit, clamped — and a separate code ceiling on top.

p.guessTotal — because an exact count evaluates far more than you display.

Failure degrades rather than propagates — a search that fails should render an empty list, not break the page.

#8.2 The alternative that avoids the query entirely

This is usually the better answer, and it's what file 05's listing model does.

/**
 * Walks the children of a KNOWN root instead of querying.
 *
 * WHY THIS IS PREFERRED:
 *   - No index needed, so nothing to deploy, maintain or get wrong
 *   - Cost is proportional to the number of children, which is bounded
 *   - Behaves IDENTICALLY on a laptop and in production -- it does not
 *     have the traversal problem's laptop/production gap
 *
 * Query only when the content could genuinely be anywhere and is
 * selected by criteria rather than by location.
 */
private List<CardModel> collectCards(String rootPath) {

    Page root = pageManager.getPage(rootPath);
    if (root == null) {
        LOG.warn("Listing root not found: {}", rootPath);
        return Collections.emptyList();
    }

    List<CardModel> cards = new ArrayList<>();
    Iterator<Page> children = root.listChildren();

    // A HARD CEILING. An author typing a bad root path should produce
    // a short list, not a repository walk that takes the instance down.
    while (children.hasNext() && cards.size() < MAX_ITEMS) {
        Page child = children.next();
        if (child.isHideInNav()) {
            continue;
        }
        Resource content = child.getContentResource();
        // adaptTo can ALWAYS return null (file 05)
        CardModel card = (content != null)
                ? content.adaptTo(CardModel.class)
                : null;
        if (card != null && card.isValid()) {
            cards.add(card);
        }
    }
    return cards;
}

#8.3 The equivalent JCR-SQL2

Worth being able to read, even if you write QueryBuilder:

SELECT * FROM [cq:Page] AS page
WHERE ISDESCENDANTNODE(page, '/content/energy/global/en/products')
  AND page.[jcr:content/productLine] = 'transformers'
ORDER BY page.[jcr:content/jcr:title]

Note the same two things: the query is scoped to a path via ISDESCENDANTNODE, and the property reference includes jcr:content/. Those aren't QueryBuilder quirks — they're properties of how AEM stores pages.

#8.4 Where a query should and shouldn't live

// WRONG -- runs on every HTL call, and HTL calls getters repeatedly
public List<String> getProducts() {
    return searchService.findProducts(resolver, productLine, 20);
}

// RIGHT -- runs ONCE, in @PostConstruct (file 05)
private List<String> products;

@PostConstruct
protected void init() {
    this.products = searchService.findProducts(resolver, productLine, 20);
}

public List<String> getProducts() {
    return products;          // just returns the field
}

And for bulk work, neither — that belongs in a Sling Job (file 10), batched, off the request path entirely. A query that's acceptable once per render is not acceptable in a loop over thousands of items.

#8.5 Diagnostic paths

# Is a query traversing, and which are slow?
/libs/granite/operations/content/diagnosistools/queryPerformance.html

# Build and test a QueryBuilder query interactively.
# FIRST STOP when a query returns unexpected results -- it will show
# you a wrong property path in under a minute.
/libs/cq/search/content/querydebug.html

# The index definitions themselves
/oak:index        (in CRXDE)

# Slow pages -- start here, then trace to the query
crx-quickstart/logs/request.log

#9. Common Mistakes

#10. Best Practices

Avoid the query first. If the content is under a known parent, walk it. That removes an entire class of problem rather than managing it, and it behaves the same everywhere.

When you must query: scope to a path, always limit, verify the index. Those three cover most of the risk.

Verify rather than assume. "It was fast when I tested it" is not a safety argument, because the thing that makes a traversing query dangerous is exactly the thing your laptop lacks.

Bound in two places. A limit on the query and a ceiling in the code — the first protects the repository, the second protects rendering.

Mind where it runs. @PostConstruct not a getter. A job not the request path. An uncached page means the query runs on every request (file 19).

Treat indexes as a real cost. Build time, permanent write overhead, maintenance. Add them deliberately.

Degrade on failure. A query that throws should render an empty list, not break the page.

Reindex after bulk content operations, and expect it to take time.

#11. Debugging Tips

When a query returns unexpected results, go to the Query Debugger first, not last. Running the query interactively and adjusting it will surface a wrong property path — the jcr:content/ trap — in about a minute, versus a long time reading Java.

When something is slow, start at request.log to find which page, then the Query Performance tool to find whether a query is traversing. Don't guess at the query from the code; ask the tool which plan Oak actually chose.

Split the problem the same way as everywhere else: is the query wrong, or is the query fine and something else slow? The Query Debugger answers the first; the Query Performance tool answers the second.

When search breaks but browsing works, it's an index problem, not a content problem. Browsing reads known paths and never touches an index.

#12. Performance Notes

Traversal is the dominant risk, and it's invisible until content volume is real. Everything else on this list matters less.

Scope beats everything else. A path predicate bounds the work even when the index isn't ideal, and it's the cheapest thing you can add.

Where the query runs multiplies its cost. Once per render is one thing; per HTL getter call is another; on an uncached page it's per request (file 19). Same query, wildly different totals.

Permission filtering costs. Oak may evaluate considerably more nodes than it returns on a restricted tree (file 13).

Exact counts cost. p.guessTotal avoids evaluating everything just to display "of about 500."

Indexes cost on write, permanently. A broad index over frequently-changing content adds overhead to every save, forever. That's the trade against query speed.

And an uncached search page has no cache protecting it — every search is a real query against publish, which is why bounding it matters more there than almost anywhere else.

#15. Comparison Tables

The three query languages

Query or walk?

Index types

Bounding a query

#Next topic

22-Core-Components-and-Style-System — Adobe's ready-made components, the proxy pattern in depth, extending via the delegation pattern, and the Style System for author-controlled appearance without new components.

Topic 21 of the AEM Interview Preparation repository. Teaching style, energy-sector project domain.