Apache OAK supports several query languages including SQL (deprecated), XPath (deprecated), JCR-JQOM, and JCR-SQL2. This guide focuses on JCR-SQL2. Note that Apache OAK enforces stricter syntax than Jackrabbit — queries that work on one platform may fail on the other.
Execution Methods
JCR API:
1
2
3
4
5
Session session = ... ;
Workspace workspace = session.getWorkspace();
QueryManager queryManager = workspace.getQueryManager();
Query query = queryManager.createQuery("{JCR-SQL2 query}", Query.JCR_SQL2);
QueryResult result = query.execute();
Sling API:
1
2
ResourceResolver resolver = ...;
Iterator<Resource> result = resolver.findResources("{JCR-SQL2 query}", Query.JCR_SQL2);
Security Note: User-supplied query data requires escaping using SQL2Parser.escapeStringLiteral() from org.apache.jackrabbit.oak.query.SQL2Parser.
Maven Dependency:
1
2
3
4
<dependency>
<groupId>org.apache.jackrabbit</groupId>
<artifactId>oak-core</artifactId>
</dependency>
Query Examples
Find Pages
1
SELECT p.* FROM [cq:Page] AS p
Selects all nodes of type cq:Page.
Cast Property Values
Queries can cast string values to property types: STRING, BINARY, DATE, LONG, DOUBLE, DECIMAL, BOOLEAN, NAME, PATH, REFERENCE, WEAKREFERENCE, URI.
1
2
SELECT p.* FROM [cq:Page] AS p
WHERE p.[jcr:created] > CAST('2015-09-22T12:33:01.042Z' AS DATE)
Find Pages by Path
1
2
SELECT p.* FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])
Find Pages by Path and Name
1
2
3
SELECT p.* FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])
AND NAME(p)='geometrixx_user_grou'
Find Pages with Inner Node Property Constraints
Access nested node properties using path notation:
1
2
3
SELECT p.* FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])
AND p.[jcr:content/author] = 'Henry Ford'
Search with LIKE
The LIKE operator supports % (zero or more characters) and _ (single character):
1
2
3
4
5
6
7
8
9
10
11
-- 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'
Full-Text Search with CONTAINS
CONTAINS enables full-text searching with wildcards (*) and OR conditions:
1
2
3
4
5
6
7
8
9
10
11
-- Basic search
SELECT p.* FROM [cq:PageContent] AS p
WHERE CONTAINS(p.[author], '{Henry Ford}')
-- Wildcard
SELECT p.* FROM [cq:PageContent] AS p
WHERE CONTAINS(p.[author], '{Hen*}')
-- Multiple conditions
SELECT p.* FROM [cq:PageContent] AS p
WHERE CONTAINS(p.[author], '{Henry} OR {Josephine} OR {John Doe}')
Note: Full-text queries depend on proper indexing; corrupted or missing indexes may return incomplete results.
JOIN Results
Apache OAK supports INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN.
INNER JOIN:
1
2
3
4
5
SELECT parent.* FROM [cq:Page] AS parent
INNER JOIN [cq:PageContent] AS child
ON ISCHILDNODE(child,parent)
WHERE ISDESCENDANTNODE(parent, [/content/geometrixx-outdoors/en])
AND child.[cq:template] = '/libs/social/blog/templates/page'
Mixin Matching:
1
2
3
4
SELECT meta.* FROM [nt:base] AS meta
INNER JOIN [cq:Taggable] AS taggable
ON ISSAMENODE(meta,taggable)
WHERE ISDESCENDANTNODE(meta, [/content/dam/geometrixx/banners])
RIGHT OUTER JOIN for Corruption Detection:
1
2
3
4
SELECT parent.* FROM [cq:Page] AS parent
RIGHT OUTER JOIN [cq:PageContent] AS child
ON ISCHILDNODE(child,parent)
WHERE ISDESCENDANTNODE(parent, [/content])
Identifies pages missing child nodes.
UNION Results
The UNION operator combines multiple SELECT statements:
1
2
3
4
5
6
7
8
9
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])