AEM JCR-SQL2: The Ultimate Recipe Book

If you remember only one thing from this guide, it is that mastering JCR-SQL2 is the difference between an AEM engineer who guesses why a feature is broken and one who diagnoses repository state in seconds. In enterprise AEM deployments, the Jackrabbit Oak repository holds millions of nodes—pages, assets, configurations, and user data. Without a precise querying mechanism, you are effectively flying blind. Most teams get this wrong because they rely on expensive, iterative Java code to traverse the JCR tree instead of leveraging the underlying Oak query engine to fetch exactly what they need in milliseconds. JCR-SQL2 is the declarative standard for interacting with the AEM repository, and knowing how to write optimized, index-backed queries is a non-negotiable skill for any Staff-level AEM engineer.

In this exhaustive guide, we cover the real-world application of JCR-SQL2 in production AEM environments. We will explore the fundamental anatomy of a JCR-SQL2 statement, the critical differences in query execution between AEM 6.5 and AEM as a Cloud Service (AEMaaCS), and how to leverage the EXPLAIN plan for performance tuning. The core of this guide is a massive recipe library containing over 20 copy-pasteable, highly optimized queries for everyday AEM tasks—ranging from finding specific component resource types and querying DAM assets, to executing complex inner joins across cq:Page and jcr:content nodes.

To fully grasp the underlying Oak indexing strategies and repository concepts discussed here, I highly recommend reviewing our core architecture materials. This post serves as the companion piece to the JCR & Oak Repository Complete Guide. You should also cross-reference the AEM Query Builder API Complete Reference for the HTTP-based abstraction over JCR-SQL2, the AEM Architecture Complete Guide for topological context, and the AEM Backend Development Complete Guide for integrating these queries into your OSGi services.

The Anatomy of a JCR-SQL2 Query

Before diving into the recipes, we must establish the structural semantics of JCR-SQL2. Unlike traditional relational SQL, JCR-SQL2 queries a hierarchical graph. The tables are Node Types, the columns are Properties, and the relationships are parent-child paths.

+-------------------+     +-------------------------+     +-----------------------+
| SELECT Clause     | --> | FROM Clause             | --> | WHERE Clause          |
| (jcr:path,        |     | (Node Type,             |     | (ISDESCENDANTNODE,    |
|  property names)  |     |  e.g., cq:PageContent)  |     |  Property Conditions) |
+-------------------+     +-------------------------+     +-----------------------+
        |
        v
+-------------------+     +-------------------------+
| ORDER BY Clause   | --> | LIMIT / OFFSET          |
| (Sorting rules)   |     | (Pagination, usually    |
+-------------------+     |  applied via Java API)  |
                          +-------------------------+

Node Types over Paths

Most developers make the mistake of querying nt:base (every node in the repository) and then filtering by path. This forces the Oak engine to scan massive subtrees. Always query the most specific node type possible. In AEM, this usually means cq:PageContent, dam:AssetContent, cq:Component, or rep:User.

AEM 6.5 vs. AEM as a Cloud Service

When writing queries, you must account for the platform.

• AEM 6.5: You have direct access to CRXDE Lite (/crx/de/index.jsp) and can use the Query tool to test JCR-SQL2 directly against your local or dev servers. Custom Lucene indexes are deployed via package manager and sit under /oak:index.
• AEM as a Cloud Service: CRXDE Lite is read-only and often locked down in higher environments. To run JCR-SQL2 queries in AEMaaCS, you must use the Developer Console (accessible via Cloud Manager). Navigate to the "Queries" tab to execute JCR-SQL2 or Explain statements. Furthermore, indexes in AEMaaCS are strictly managed via the ui.config project and the Blue/Green deployment pipeline.

Executing JCR-SQL2

Via CRXDE Lite (AEM 6.5 / Local)

1. Open http://localhost:4502/crx/de/index.jsp.
2. Click Tools > Query in the top menu.
3. Select Type: JCR-SQL2.
4. Paste your query and click Execute.

Via Developer Console (AEMaaCS)

1. Log into Adobe Cloud Manager.
2. Select your Program and Environment.
3. Click the Developer Console icon.
4. Navigate to the Queries tab.
5. Select JCR-SQL2 from the dropdown, paste the query, and run.

Via Java API (OSGi Services)

In backend development, you execute JCR-SQL2 using the JCR QueryManager. Never hardcode queries; use OSGi configurations or constants, and always use parameterized values to prevent injection, though JCR-SQL2 doesn't have a standard bind variable syntax natively supported across all implementations without specific Sling abstractions.

import javax.jcr.NodeIterator;
import javax.jcr.Session;
import javax.jcr.query.Query;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;

public void executeQuery(Session session) throws Exception {
    QueryManager queryManager = session.getWorkspace().getQueryManager();
    String sql2 = "SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE([/content/we-retail]) AND s.[cq:template] = '/conf/we-retail/settings/wcm/templates/hero-page'";
    
    Query query = queryManager.createQuery(sql2, Query.JCR_SQL2);
    // Best practice: Always set a limit to prevent memory exhaustion
    query.setLimit(500);
    
    QueryResult result = query.execute();
    NodeIterator nodeIter = result.getNodes();
    
    while (nodeIter.hasNext()) {
        javax.jcr.Node node = nodeIter.nextNode();
        // Process node
    }
}

The JCR-SQL2 Recipe Library

Here is the definitive collection of JCR-SQL2 queries. Every Staff engineer should keep these bookmarked.

1. The Basics: Finding Nodes by Path and Type

The most common requirement is finding all nodes of a certain type under a specific path.

Recipe: Find all Page Content nodes under a specific site path.

SELECT * FROM [cq:PageContent] AS p 
WHERE ISDESCENDANTNODE(p, [/content/wknd/us/en])

Why this works: [cq:PageContent] restricts the search to page properties nodes (the actual content). ISDESCENDANTNODE is a highly optimized JCR function that uses path-based indexing.

2. Filtering by Template

When authors request a list of all "Article" pages, you query by cq:template.

Recipe: Find all pages using a specific Editable Template.

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

3. Finding Specific Component Instances

If you are deprecating an old component and need to know every page where it is authored, you query for the sling:resourceType. Because components are unstructured nodes under jcr:content, we query nt:unstructured.

Recipe: Find all instances of the "Carousel" component.

SELECT * FROM [nt:unstructured] AS comp 
WHERE ISDESCENDANTNODE(comp, [/content/wknd]) 
AND comp.[sling:resourceType] = 'wknd/components/carousel'

4. Full-Text Search (Contains)

The CONTAINS() function triggers the underlying Lucene (or Elastic in AEMaaCS) full-text index. This is powerful but can be expensive if not scoped.

Recipe: Search the entire WKND site for the word "adventure".

SELECT * FROM [cq:PageContent] AS p 
WHERE ISDESCENDANTNODE(p, [/content/wknd]) 
AND CONTAINS(p.*, 'adventure')

Recipe: Search specifically within the jcr:title property.

SELECT * FROM [cq:PageContent] AS p 
WHERE ISDESCENDANTNODE(p, [/content/wknd]) 
AND CONTAINS(p.[jcr:title], 'adventure')

5. Querying Digital Assets (DAM)

The DAM is structured differently. The root is dam:Asset, and the metadata sits at dam:AssetContent under jcr:content/metadata.

Recipe: Find all PDF documents in a specific DAM folder.

SELECT * FROM [dam:Asset] AS a 
WHERE ISDESCENDANTNODE(a, [/content/dam/wknd]) 
AND a.[jcr:content/metadata/dc:format] = 'application/pdf'

Recipe: Find assets larger than 5 Megabytes.

SELECT * FROM [dam:Asset] AS a 
WHERE ISDESCENDANTNODE(a, [/content/dam/wknd]) 
AND CAST(a.[jcr:content/renditions/original/jcr:content/jcr:data] AS Long) > 5242880

Note: We use CAST() to ensure the binary size is evaluated as a Long integer.

6. Working with Dates

Date queries require standardizing the timestamp format. AEM uses the CAST('YYYY-MM-DDTHH:MM:SS.000Z' AS DATE) syntax.

Recipe: Find pages modified after a specific date.

SELECT * FROM [cq:PageContent] AS p 
WHERE ISDESCENDANTNODE(p, [/content/wknd]) 
AND p.[cq:lastModified] >= CAST('2026-01-01T00:00:00.000Z' AS DATE)

Recipe: Find assets expiring in the next 30 days.

SELECT * FROM [dam:AssetContent] AS a 
WHERE ISDESCENDANTNODE(a, [/content/dam/wknd]) 
AND a.[metadata/prism:expirationDate] <= CAST('2026-12-06T00:00:00.000Z' AS DATE)
AND a.[metadata/prism:expirationDate] >= CAST('2026-11-06T00:00:00.000Z' AS DATE)

7. Identifying Null or Missing Properties

Sometimes you need to find nodes that lack a mandatory property, such as pages without an SEO description.

Recipe: Find pages missing a jcr:description.

SELECT * FROM [cq:PageContent] AS p 
WHERE ISDESCENDANTNODE(p, [/content/wknd]) 
AND p.[jcr:description] IS NULL

Recipe: Find pages where jcr:description exists (IS NOT NULL).

SELECT * FROM [cq:PageContent] AS p 
WHERE ISDESCENDANTNODE(p, [/content/wknd]) 
AND p.[jcr:description] IS NOT NULL

8. Utilizing LIKE and Wildcards

The LIKE operator allows for pattern matching. % matches zero or more characters, and _ matches a single character.

Recipe: Find pages where the title starts with "Summer".

SELECT * FROM [cq:PageContent] AS p 
WHERE ISDESCENDANTNODE(p, [/content/wknd]) 
AND p.[jcr:title] LIKE 'Summer%'

9. Finding Nodes by Mixin Types

Mixins add behavioral traits to nodes. For example, mix:versionable means the node history is tracked, and cq:LiveSync indicates the node is part of a Live Copy.

Recipe: Find all Live Copy root pages (MSM).

SELECT * FROM [cq:PageContent] AS p 
WHERE ISDESCENDANTNODE(p, [/content/wknd]) 
AND [jcr:mixinTypes] = 'cq:LiveSync'

Recipe: Find all nodes that are taggable but not strictly pages.

SELECT * FROM [nt:unstructured] AS n 
WHERE ISDESCENDANTNODE(n, [/content/wknd]) 
AND [jcr:mixinTypes] = 'cq:Taggable'

10. Querying Users and Groups (Security)

Security data lives under /home. Users are rep:User and groups are rep:Group.

Recipe: Find a user by their email address.

SELECT * FROM [rep:User] AS u 
WHERE ISDESCENDANTNODE(u, [/home/users]) 
AND u.[profile/email] = 'author@wknd.com'

Recipe: Find all groups that have "approvers" in the name.

SELECT * FROM [rep:Group] AS g 
WHERE ISDESCENDANTNODE(g, [/home/groups]) 
AND g.[rep:principalName] LIKE '%approvers%'

11. Complex INNER JOINs

This is where JCR-SQL2 outshines XPath. You can join a parent node to a child node to filter on properties of both simultaneously.

Recipe: Find all cq:Page nodes (the page wrapper) where the underlying cq:PageContent has a specific tag.

SELECT page.* FROM [cq:Page] AS page 
INNER JOIN [cq:PageContent] AS content ON ISCHILDNODE(content, page) 
WHERE ISDESCENDANTNODE(page, [/content/wknd]) 
AND content.[cq:tags] = 'wknd:activity/surfing'

Why this works: We select the cq:Page node, but we join it with its child cq:PageContent node, allowing us to filter on the content's tags while returning the actual page node.

Recipe: Find Assets based on Rendition properties.

SELECT asset.* FROM [dam:Asset] AS asset
INNER JOIN [nt:file] AS rendition ON ISDESCENDANTNODE(rendition, asset)
WHERE ISDESCENDANTNODE(asset, [/content/dam])
AND rendition.[jcr:content/jcr:mimeType] = 'image/webp'

12. Using LocalName() and Name()

Sometimes you need to query based on the exact name of the node in the JCR tree.

Recipe: Find all nodes named precisely jcr:content.

SELECT * FROM [nt:base] AS n 
WHERE ISDESCENDANTNODE(n, [/content/wknd]) 
AND LOCALNAME(n) = 'jcr:content'

13. Advanced Multi-condition Query (The "Active Campaign" query)

Combining everything into a production-realistic query.

Recipe: Find all English Article pages that are currently valid (published, not expired), tagged with "summer", and have an SEO description.

SELECT p.* FROM [cq:PageContent] AS p 
WHERE ISDESCENDANTNODE(p, [/content/wknd/us/en]) 
AND p.[cq:template] = '/conf/wknd/settings/wcm/templates/article-page'
AND p.[cq:tags] = 'wknd:season/summer'
AND p.[jcr:description] IS NOT NULL
AND (p.[offTime] IS NULL OR p.[offTime] > CAST('2026-11-06T00:00:00.000Z' AS DATE))
AND (p.[onTime] IS NULL OR p.[onTime] < CAST('2026-11-06T00:00:00.000Z' AS DATE))
ORDER BY p.[cq:lastModified] DESC

14. Finding Orphan Nodes

Orphan nodes (nodes that exist but are disconnected from standard application logic, like a component without a parent paragraph system) are tricky. Usually, you look for specific structures.

Recipe: Find components directly under jcr:content instead of in a root parsys.

SELECT comp.* FROM [nt:unstructured] AS comp
INNER JOIN [cq:PageContent] AS page ON ISCHILDNODE(comp, page)
WHERE ISDESCENDANTNODE(page, [/content/wknd])
AND comp.[sling:resourceType] IS NOT NULL

(This targets unstructured nodes directly attached to PageContent, bypassing the layout container).

Query Diagnostics: The EXPLAIN Statement

Never run a query in production without prefixing it with EXPLAIN. The Explain statement tells you exactly which Oak index the query engine will use, or worse, if it will resort to a full traversal.

Syntax:

EXPLAIN MEASURE SELECT * FROM [cq:PageContent] WHERE ISDESCENDANTNODE([/content/wknd]) AND [cq:template] = '/conf/wknd/settings/wcm/templates/article-page'

Understanding the Output: When you run this in CRXDE or the Developer Console, look for the plan column.

• Good Plan: [cq:PageContent] as p /* lucene:cqPageLucene(/oak:index/cqPageLucene) ... This means it successfully hit a Lucene index.
• Bad Plan: [cq:PageContent] as p /* traverse "/content/wknd" ... This is a traversal warning. If the path has more than 100,000 nodes, AEM will throw a TraversalException and crash the query.

If you see a traversal, you MUST create or update an Oak index under /oak:index to cover the properties you are querying (e.g., adding cq:template to the property index).

JCR-SQL2 Cheat Sheet

Best Practices

1. Never use [nt:base]: Always restrict your query to the most specific node type (cq:PageContent, dam:Asset, rep:User). Querying nt:base bypasses specialized indexes and forces massive scans.
2. Always scope with ISDESCENDANTNODE: Never search the entire repository. Always limit the query to /content/mysite, /content/dam/myfolder, or /home.
3. Set limits in Java: JCR-SQL2 does not have a LIMIT keyword natively in the string syntax. You MUST call query.setLimit(int) in your Java code to prevent OutOfMemory errors on large result sets.
4. Use QueryBuilder for HTTP, JCR-SQL2 for Backend: If you are writing a frontend application making AJAX calls, use the AEM QueryBuilder API. If you are writing internal Java OSGi services, use JCR-SQL2 via the QueryManager for maximum performance and type safety.

Do's & Don'ts

• DO use the Developer Console in AEM as a Cloud Service to test queries before committing code.
• DO use EXPLAIN MEASURE to verify that your query uses an index.
• DON'T use JCR-SQL2 to iterate over every node just to read a single property; if you know the exact path, use session.getNode(path)—it is orders of magnitude faster.
• DON'T perform string manipulation inside JCR-SQL2. The query language is limited; fetch the nodes and do complex data extraction in Java.
• DON'T forget that AEM applies ACLs (Access Control Lists) to query results. A query executed by a content-author will only return nodes they have permission to read, whereas the same query run by an administrative service user will return everything.

Mastering these recipes will significantly reduce your debugging time and ensure your backend AEM code is performant, scalable, and robust. Keep this recipe book handy, and always check your Explain plans!