CIRCUIT

Querying the JCR with SQL2 in AEM: a hands-on guide

Adobe Experience Manager stores every page, asset, component and metadata fragment inside a Java Content Repository. The JCR is the foundation that powers authoring, replication, search and personalisation across the platform, and for developers working on AEM projects, knowing how to query that repository efficiently is the difference between a sluggish site and a content platform that scales gracefully under load.

SQL2 is the modern query language that the JCR 2.0 specification introduced to replace the older JCR 1.0 dialect. It reads much closer to standard SQL, supports joins, full-text searches, IS NOT NULL checks, ORDER BY clauses and bind variables, and it is the recommended approach for new AEM code. Most AEM teams in Australia, from the big four banks in Sydney and Melbourne to government portals like myGov and the ATO, have spent the last few years migrating legacy XPath queries over to SQL2 for exactly these reasons.

For front-end developers, SQL2 often feels invisible because Sightly templates and HTL components just bind to Sling Models. Under the bonnet, however, every list, every autocomplete dropdown and every cached navigation is backed by a query against the repository. Understanding the language helps you debug slow pages, write smarter services and avoid the production outages that come from badly indexed traversals.

This guide walks through the practical side of querying the JCR with SQL2 in AEM, from the first SELECT statement to performance tuning on author clusters in geographically distributed setups. Along the way you will see Australian examples, a comparison table of the query options available, and a few patterns pulled from real AEM implementations.

SQL2, XPath and the QueryBuilder: how the three compare

Before writing any code, it is worth knowing where SQL2 sits alongside the other two query mechanisms you can use in AEM. XPath was the original JCR 1.0 query language and is still around for backward compatibility. The Sling QueryBuilder is a Java API that accepts a map of predicates and translates them into XPath or SQL2 under the hood. SQL2 itself is executed against the javax.jcr.query.Query and QueryManager interfaces and is parsed directly by the JCR implementation, usually Oak.

Feature SQL2 XPath QueryBuilder
Spec status JCR 2.0 standard JCR 1.0 legacy Sling layer on top
Joins Native INNER, LEFT, RIGHT Limited via descendant axis Not directly, simulated
Bind variables Yes (:name) No No direct support
Full-text search LIKE and CONTAINS jcr:contains fulltext predicate
Ordering ORDER BY orderby axis orderby predicate
Pagination LIMIT and OFFSET Manual node iteration p.offset, p.limit
Performance under Oak Best when indexed Often falls back to traversal Depends on generated query
Best use case Services, servlets, OSGi components Quick console checks HTL Use objects, fast prototyping

For new AEM development the rule of thumb across Australian teams is simple: use SQL2 inside Java services, keep the QueryBuilder for HTL-driven Use objects where readability matters, and reach for XPath only when you are debugging inside the CRXDE Lite query tool. The next section shows what a clean SQL2 query actually looks like.

Writing your first SQL2 query in an AEM service

A working SQL2 example starts with a Session, moves through a QueryManager and finishes with a QueryResult that yields NodeIterator rows. The pattern below is the one you will find in most training sessions run by Adobe partner consultancies in Sydney and Melbourne.

Map<String, Object> params = new HashMap<>();
params.put("type", "cq:Page");
params.put("template", "/conf/myapp/settings/wcm/templates/article");

String stmt = "SELECT * FROM [cq:Page] AS page "
            + "WHERE page.[jcr:content/cq:template] = :template "
            + "AND ISDESCENDANTNODE(page, '/content/myapp') "
            + "ORDER BY page.[jcr:content/jcr:title]";

Query query = queryManager.createQuery(stmt, "JCR-SQL2");
query.bindValues(params);
QueryResult result = query.execute();
NodeIterator nodes = result.getNodes();

The square brackets around [cq:Page] and [jcr:content/cq:template] are mandatory so the Oak parser can tell node types and property paths from reserved keywords. The ISDESCENDANTNODE function is the SQL2 equivalent of an XPath descendant axis and is the most efficient way to scope a query to a branch of the content tree. Bind variables protect you from injection when the value is coming from a request parameter or a workflow payload.

If you need to query across multiple node types, joins are where SQL2 really earns its keep. A common Australian pattern is to fetch a list of pages together with their last replicated version, then build a sitemap entry from the result set. The join syntax is verbose but readable once you have written it a few times, and it removes the need for a second round-trip from the repository.

Performance, indexes and the Oak query engine

SQL2 only performs well when Oak can serve the query from a Lucene or property index. If no index matches, the JCR implementation falls back to a full traversal, which on a large author instance can lock the repository for seconds and trigger JCR observation storms that ripple out to publishers. Most Australian AEM teams run a centralised author cluster in Sydney with publishers spread across regional data centres, so a slow query on author is felt immediately by content authors in Perth or Brisbane.

The first thing to check is whether your query has a leading path constraint. ISDESCENDANTNODE(node, '/content/myapp') is indexable, while a bare WHERE clause without a path scope usually is not. The second is whether the properties in your WHERE clause appear in any index definition under /oak:index. The Oak Index Tools GTM component in CRXDE Lite lets you paste a statement and see the execution plan. If the plan shows traversal instead of lucene, the query will not scale.

A practical tip from a few large Australian retail projects: declare a lucene index for any custom node type that is queried more than once per request. Include jcr:primaryType, the property you are filtering on, and any ORDER BY columns in the indexRules. Keep the reindex flag on a development instance first, then promote the change through staging and production during a quiet maintenance window. Index definitions on a live author are one of the few changes that require a restart of the Oak bundle, so coordinate with your DevOps lead before you ship the new configuration.

When assets arrive in the repository from other design tools, a well-designed index also keeps the downstream SQL2 queries that power search facets and sitemaps reliable. A look at how other Adobe ecosystem tools feed content into AEM pipelines shows why the team that owns the index definition needs to coordinate with design and asset production, not just the Java developers.

Common pitfalls and patterns from Australian AEM teams

Developers who cut their teeth on JCR 1.0 often carry XPath habits into SQL2, and a few of those habits actively hurt performance. A frequent mistake is treating LIKE as a generic substring search: in SQL2 the wildcard placement matters, and LIKE 'news/%' can use an index while LIKE '%news%' cannot. Another is forgetting that node types are case-sensitive in Oak, so [cq:page] returns zero rows while [cq:Page] returns the expected results. Bind variables are also case-sensitive, and a missing colon in :template silently produces an empty result set that can be tricky to spot in code review.

Replication paths are another common source of confusion. If your service runs on a publish instance, the query needs to respect whatever replication filters the dispatcher applies, otherwise you may serve nodes that have not been published yet. A solid write-up on replication troubleshooting in AEM covers the surrounding workflow in more detail.

A few patterns are standard across the Australian AEM community, particularly inside financial services and federal agencies where regulatory requirements push teams toward specific repository structures. One pattern is a metadata sub-node on every component instance, queried through SQL2 to power faceted search on intranet portals. Another is the archival of expired content using lifecycle workflows that move nodes to a cold storage branch before deletion, keeping the active tree small enough for SQL2 to remain fast.

For mobile experiences, several Australian publishers have shipped AEM-driven content inside hybrid apps using PhoneGap-style wrappers, a workflow that is documented in the CIRCUIT session on PhoneGap mobile apps. SQL2 plays a role in that pipeline because the content service that feeds the mobile app is itself a SQL2-backed servlet rather than a static JSON dump, and any slowdown in the query is felt directly on the device.

Recommendations for SQL2 in production AEM

  • Stick to SQL2 in Java services and keep the QueryBuilder only for HTL Use objects where the predicate map is easier to read.
  • Add an Oak lucene index for every custom node type that appears in a WHERE clause, and verify the execution plan in CRXDE Lite before shipping.
  • Always scope queries with ISDESCENDANTNODE and use bind variables rather than string concatenation.
  • Run the Oak Index Tools GTM against every query you add, and include a unit test that asserts the plan resolves through an index rather than a traversal.
  • For geographically distributed author setups, log slow queries through a JCR observation listener and review the list with the team every sprint.

Sharpen your AEM skills further by browsing the recordings from the CIRCUIT 2015 and 2016 conferences in Chicago, which cover full-length sessions on AEM integrations, Sightly, microservices and architecture. Subscribing to the journal keeps new write-ups, troubleshooting guides and post-event updates in your inbox, so the next time a SQL2 query misbehaves in production you have a reference within reach.