JCR-SQL2 Queries For AEM / Adobe CQ Developer

JCR-SQL2 queries are useful items in every AEM Developer's toolbag.  They are relatively quick, aren't deprecated and have a familiar SQL like grammer. JCR-SQL2 queries can be invoked from the JCR API:

Session session = ...
QueryManager queryManager = session.getWorkspace.getQueryManager();
Query query = queryManager.createQuery("{QUERY}", Query.JCR_SQL2);
QueryResult result = query.execute();

or through the Sling API:

ResourceResolver resolver = ...
Iterator<Resource> result = resolver.findResources("{QUERY}", Query.JCR_SQL2);

Although there is an official specification for JCR-SQL2, there aren't a ton of really good resources out there nor good references for finding examples of JCR-SQL2 queries.  Below are some queries I've found useful and I think every developer should have as a reference.  Each item lists a concept, provides an sample query and explains the sample.

1. Find nodes by type

SELECT * FROM [nt:file] AS s

This query will find all of the nt:file nodes in the repository.  Users often start by querying against nt:base which is the base type for all JCR nodes, however if you can limit down to a more specific node type, this can have dramatic performance differences.  Just remember that the node containing the properties you're querying against needs to either be the specified type or a decendent of the type.

2. Find nodes with properties containing a value

SELECT * FROM [nt:base] AS s WHERE CONTAINS(s.title, 'client')

This query will find any node with a title property which contains the text client. This can also be accomplished using the LIKE constraint, but is significantly faster as it invokes the Lucene indexes instead of scanning property values.  In a very basic test, I found using CONTAINS to be approximately 100x faster.

3. Finding pages under a path:

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE(s,'/content')

This query will select all nodes of the type cq:Page under the path /content. You could change the node type in the square brackets to query in other node types such as dam:Asset or rep:user.

4. Finding nodes by name:

SELECT * FROM [cq:Page] WHERE NAME() = 'content'

This query will retrieve any cq:Page node with the name "content"; if you used the LOCALNAME function instead of NAME it would ignore the node type prefix, matching names such as jcr:content.

5. Finding non-empty properties

SELECT * FROM [cq:PageContent] WHERE [jcr:title] IS NOT NULL

This query will retrieve cq:PageContent nodes where the jcr:title attribute is set. 

Note: Pages are not required to have a child of type cq:PageContent so if your templates don't set the child node type this may not always return all of the eligible nodes.

6. Finding pages based on jcr:content properties

SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:base] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, '/content') AND child.[cq:template] = '/libs/cq/personalization/templates/campaign'

This query returns cq:Page nodes with a child node where the cq:template is set to /libs/cq/personalization/templates/campaign.The really nice thing is that it returns the actual page node instead of the child node which contains the value.

7. Finding files with extension

SELECT * FROM [nt:file] WHERE NAME() LIKE '%.png'

This query will find files with the .png extension. This can be very useful when looking for bundles, assets or other files which have been uploaded.  To search the DAM, change the node type to dam:Asset.

8. Cast Property Values

SELECT * FROM [nt:base] WHERE hideInNav = CAST('true' AS BOOLEAN)

Casting allows you to query accurately based on non-String properties.  You can cast into any of the following types:

• STRING
• BINARY
• DATE
• LONG
• DOUBLE
• DECIMAL
• BOOLEAN
• NAME
• PATH
• REFERENCE
• WEAKREFERENCE
• URI

9. Querying on dates

SELECT p.* FROM [nt:base] AS p WHERE p.[cq:lastModified] >= CAST('2008-01-01T00:00:00.000Z' AS DATE) AND p.[cq:lastModified] <= CAST('2008-12-31T23:59:59.999Z' AS DATE)

This query will find any node which has been modified in 2008. Date conversion for JCR-SQL2 can be somewhat interesting as it expects the date parameter to be in the format 2008-01-01T00:00:00.000Z which is not possible to easily produce with a SimpleDateFormat. One method for getting the date in the correct format is:

Calendar cal;
ValueFactoryImpl.getInstance().createValue(cal).getString();

Wrapup

Hopefully, you find these query examples useful, if you see any mistakes or anything I missed, please leave a comment!

References:

• http://docs.jboss.org/jbossdna/0.7/manuals/reference/html/jcr-query-and-search.html#d0e7872
• http://stackoverflow.com/questions/5231411/jackrabbit-dateformat-for-comparison