Query Alternatives in JCR

JCR 2.0 provides two query syntaxes:

  • JCR-SQL2 — SELECT * FROM [nt:unstructured] (preferred)
  • XPath — //element(*, nt:unstructured) (officially deprecated in spec but fully supported by Oak)

JCR-SQL (v1) is deprecated — do not use it. Queries can be executed using either Sling or JCR Java API.

XPath vs JCR-SQL2

  • XPath is transformed internally to JCR-SQL2
  • XPath syntax is more compact for simple queries
  • Some special features only supported by JCR-SQL2
  • JCR-SQL2 is preferred

All queries only return results for nodes for which the current user has at least read permission (ACLs are always enforced).

Query Examples Overview

  • Find nodes of a given type
  • Find nodes under a given path
  • Find pages by property (single, multiple, multi-valued)
  • Find pages with property not set
  • Full text search
  • Filter by date
  • Ordering results (by path, score, property)

Important: Always filter for a primary node type for which an Oak index is defined. Never query without node type (traversal query = inefficient).

JCR-SQL2 Query Examples

-- Find all pages
SELECT * FROM [nt:unstructured]
  WHERE ISDESCENDANTNODE('/content/matrix')
  AND [sling:resourceType] = 'sling-matrix/pages/contentpage'

-- Find pages by property
SELECT * FROM [nt:unstructured] AS node
  WHERE ISDESCENDANTNODE(node, '/content/matrix')
  AND node.[sling:resourceType] = 'sling-matrix/pages/contentpage'
  AND node.[jcr:title] IS NOT NULL

-- Find pages with property not set (hideInNav)
SELECT * FROM [nt:unstructured] AS node
  WHERE ISDESCENDANTNODE(node, '/content/matrix')
  AND node.[hideInNav] IS NULL

-- Full text search
SELECT * FROM [nt:unstructured] AS node
  WHERE ISDESCENDANTNODE(node, '/content/matrix')
  AND CONTAINS(node.*, 'matrix')

-- Order by property
SELECT * FROM [nt:unstructured] AS node
  WHERE ISDESCENDANTNODE(node, '/content/matrix')
  ORDER BY node.[jcr:created] DESC

Execute Query via Sling API

String queryViaSling(ResourceResolver resolver) {
    Iterator<Resource> resources = resolver.findResources(
        "/jcr:root/content/matrix//element(*, nt:unstructured)"
        + "[sling:resourceType='sling-matrix/pages/contentpage']",
        "xpath");

    StringBuilder output = new StringBuilder();
    while (resources.hasNext()) {
        Resource resource = resources.next();
        output.append("path=" + resource.getPath() + "n");
    }
    return output.toString();
}

Execute Query via JCR API

String queryViaJcr(Session session) throws RepositoryException {
    QueryManager queryManager = session.getWorkspace().getQueryManager();

    Query query = queryManager.createQuery(
        "SELECT * FROM [nt:unstructured] AS node"
        + " WHERE ISDESCENDANTNODE(node, '/content/matrix')"
        + " AND node.[sling:resourceType] = 'sling-matrix/pages/contentpage'",
        Query.JCR_SQL2);

    QueryResult result = query.execute();
    NodeIterator nodes = result.getNodes();
    StringBuilder output = new StringBuilder();
    while (nodes.hasNext()) {
        Node node = nodes.nextNode();
        output.append("path=" + node.getPath() + "n");
    }
    return output.toString();
}

Advanced JCR Query Features

  • JCR-SQL2 supports joins similar to RDBMS SQL (but may not be performant on large results)
  • Tabular data — results as rows/columns instead of nodes (both Sling and JCR API support this)
  • Logical operators — AND, OR with brackets for complex filtering. Beware: OR may result in multiple query runs.
  • Advanced operators: LIKE, ISCHILDNODE, ISDESCENDANTNODE, ISSAMENODE, CONTAINS

Escaping Query Parameters

To prevent query injection threats, proper escaping is required for paths and strings from unsafe sources (e.g. URL parameters):

  • ISO9075.encodePath() — encode paths (XPath only)
  • Escape.jcrQueryLiteral() — escape string values (XPath and JCR-SQL2)
  • Escape.jcrQueryContainsExpr() — escape strings in jcr:contains calls

Alternatively: use named parameters in JCR-SQL2 with $variableName syntax and Query.bindValue() (JCR API only).

Oak Indexing

  • In Jackrabbit Oak, per default no indexes are defined — they must be explicitly created
  • Always check in query plan that an Oak index is used, not the "traversal index" (iterating all nodes = very slow)
  • Most trivial queries use one of the default indexes
  • Sometimes tweaking the query slightly can make use of a predefined index
  • Otherwise create your own index for your use case

Debugging Queries

Set DEBUG logging levels for:

  • org.apache.jackrabbit.oak.query
  • org.apache.jackrabbit.oak.plugins.index

Testing tools: CRX DE Lite (Tools → Query), Groovy Console, Query Performance Dashboard.