CQ Queries Demystified

2014-10-09

Tags: CQ5-AEM, JCR, Queries

The JCR (Java Content Repository) is a data-store at its heart. Most examples around it demonstrate either building components or walking the Node tree. It is hard to find examples around queries and the JCR. To make things worse, we have a number of options and little resourcs to compare them. My humble goal is to make JCR queries less scary - to myself and others.

Why Use Queries?

I see 2 primary benefits to using queries compared to "walking the nodes": efficiency and flexibility.

Queries can be more efficient when the desired nodes/pages are "sparse". By sparse, I mean you have to visit a lot more nodes than you keep. In my recent experience, I was looking for approximately 200-300 pages out of 2,000. Converting from page traversal to a query improved performance by an order of magnitude (from approximately 10 sec. to well under a second.)

Queries also permit flexibility in structure. David’s Rules encourage prioritizing content over formal structure. If walking the tree of nodes or pages, a developer may limit depth of searching (either a flat level or limited recursion) for simplicity of code and/or performance. However, in a CMS environment, there are tremendous benefits for authors when they can create an arbitrary taxonomy around their data or pages.

There are two primary APIs for Querying in CQ5:

  • javax.jcr.query provides a query interface API in a variety of syntaxes. QueryResult can return NodeIterators or RowIterators (where a Row can have 1 or more columns. Columns can be http://www.day.com/specs/javax.jcr/javadocs/jcr-2.0/javax/jcr/Node.html[Node] node, http://www.day.com/specs/javax.jcr/javadocs/jcr-2.0/javax/jcr/Value.html[Value] property, String path or double score).

  • com.day.cq.search provides both a REST interface and an API. The SearchResult can return Iterator<Resource>, Iterator<Node>, or List<http://docs.adobe.com/docs/en/aem/6-0/develop/ref/javadoc/com/day/cq/search/result/ResultPage.html[ResultPage]> that match the Predicates.

Preconditions

CQ5 searches are "powered by" Apache Lucene (although AEM6 is converting to Apache Solr). An important limitation is that indexing only examines Node properties that are 16KB or less in size - so anything larger won’t be found by any searching mechanism.

Internally, the queries are converted to an AQM (Abstract Query Model). The interesting aspect about AQM is the ability to create new query syntaxes and predicates - but that is well beyond the scope of this post.

NodeTypes

I recommend paying attention to the NodeType you use. At the top of the hierarchy tree is nt:base, (so all nodes inherit from it). Because of this inheritance, most examples show using it as a "works anywhere" approach. However, I feel this is a poor approach and you would be better served selecting a more appropriate type (jcr:primaryType property of your desired nodes). I would like to highlight:

  • cq:Page to match just Page nodes

  • cq:PageContent to easily convert to InheritanceValueMap or ValueMap

  • nt:unstructured to find content nodes for generic components or content

JCR Queries: javax.jcr.query

Table 1. jcr.query Syntaxes
Syntax Cons Pros

SQL2

poor documentation
no sub-queries
limited joins
new functions

"looks" like a query
limited joins (K.I.S.S. principle)

XPath

"strange" syntax (Tutorials exist)
deprecated in JCR2.0 (yet still supported)
many XPath functions missing

maturity & performance
generic (outside JCR) solution for structure/nodes/attributes

JQOM

poor documentation

build own query language/API

SQL

no longer supported

N/A
Query Example
<%--
  JCR Query API component.
  Component for dumping list of components found
--%><%@page session="false" %>
<%@include file="/libs/foundation/global.jsp"%>
<%@page import="javax.jcr.query.Query, java.util.Iterator" %>
<h2>Components from javax.jcr.query</h2>
<ul>
    <li># <strong>Component Title</strong> path</li>
<%
    // final String xpath = "/jcr:root/apps//element(*, cq:Component) order by @jcr:title";
    // Iterator<Resource> rscIterator = resourceResolver.findResources(xpath, Query.XPATH);
    final String sql2 = "SELECT * FROM [cq:Component] AS c WHERE ISDESCENDANTNODE([/apps]) ORDER BY lower(c.[jcr:title])";
    Iterator<Resource> rscIterator = resourceResolver.findResources(sql2, Query.JCR_SQL2);
%>
  <c:forEach var="compRsc" items="<%= rscIterator %>" varStatus="i">
    <%-- conversion steps to aid in getting Resource properties --%>
    <c:set var="compProps" value="<%= ((Resource)pageContext.getAttribute("compRsc")).adaptTo(ValueMap.class) %>" />
    <li>${i.count}: <strong>${compProps['jcr:title']}</strong> ${compRsc.path}</li>
  </c:forEach>
</ul>
Tip
 Prefer Resource over Node. It has a cleaner, easier to use API.
Tip
 Leverage JSP Expression Language & JSTL to keep the JSP Template simple & clear.

QueryBuilder API: com.day.cq.search

Table 2. QueryBuilder Syntaxes
Syntax Cons Pros

Java API

More code to setup
node property values must be extracted (just like JCR Queries)

Iterators for Resources, Nodes (your choice)
Lists of Pages (natural for search results pages)
Predicates make "sense" as search conditions

"RESTful" interface at /bin/querybuilder.json

grouping predicates is more complex (naming predicates)

dynamic at runtime
documented examples of multi properties/values/nesting
easy to test/explore via browser, curl
Query Example
<%--
  CQ Query API component.
  Component for dumping list of components found
--%><%@page session="false" %>
<%@include file="/libs/foundation/global.jsp"%>
<%@page import="java.util.*, com.day.cq.search.*, com.day.cq.search.result.*" %>
<h2>Components from com.day.cq.search (QueryBuilder)</h2>
<ul>
    <li># <strong>Component Title</strong> path</li>
<%
    Map<String, String> predicates = new HashMap<String, String>() {{
        put("path", "/apps");
        put("type", "cq:Component");
        put("orderby", "@jcr:title");
    }};

    QueryBuilder qb = resourceResolver.adaptTo(QueryBuilder.class);
    Session session = resourceResolver.adaptTo(Session.class);

    Query query = qb.createQuery(PredicateGroup.create(predicates), session);
    query.setHitsPerPage(0); // return ALL results
    Iterator<Resource> rscIterator = query.getResult().getResources();
%>
  <c:forEach var="compRsc" items="<%= rscIterator %>" varStatus="i">
    <%-- conversion steps to aid in getting Resource properties --%>
    <c:set var="compProps" value="<%= ((Resource)pageContext.getAttribute("compRsc")).adaptTo(ValueMap.class) %>" />
    <li>${i.count}: <strong>${compProps['jcr:title']}</strong> ${compRsc.path}</li>
  </c:forEach>
</ul>
Tip
 JSPs are best kept as templates with little to no Java in them. In practice, move the Java to an OSGi-managed class (Model). If built outside of CQ5, this permits rapid unit testing and potentially other JVM languages such as groovy.

REST Example: http://localhost:4502/bin/querybuilder.json?type=cq:Component&path=/apps&orderby=@title&orderby.sort=asc

Other Resources

SQL2 functions (from JCR v2.0 Spec)

  • CONTAINS(propName, value)

  • ISCHILDNODE() immediate relationship

  • ISDESCENDANTNODE() nested relationship

  • ISSAMENODE() for join conditions

  • LENGTH(propName)

  • NAME()

  • LOCALNAME()

  • LOWER() lower case the text

  • UPPER() upper case the text

  • SCORE()

  • CAST(literal AS type)