How do I run a JCR-SQL2 query from a Groovy script in Jahia?

Question

How do I run a JCR-SQL2 query from a Groovy script in Jahia?

Answer

Open a session through JCRTemplate, get the query manager from the workspace, and run the query:

import org.jahia.services.content.JCRTemplate
import org.jahia.services.content.JCRSessionWrapper
import org.jahia.services.content.JCRCallback
import javax.jcr.query.Query

JCRTemplate.getInstance().doExecuteWithSystemSession(null, "default", { JCRSessionWrapper session ->

    String q = "SELECT * FROM [nt:file] WHERE isdescendantnode('/sites/mySite')"

    def nodes = session.getWorkspace().getQueryManager()
            .createQuery(q, Query.JCR_SQL2)
            .execute()
            .getNodes()

    while (nodes.hasNext()) {
        def node = nodes.nextNode()
        println node.getPath()
    }
    return null
} as JCRCallback)

Pass "live" instead of "default" to query the live workspace.

Iterating the result

getNodes() returns a MultipleNodeIterator. It implements both java.util.Iterator and Iterable, so all three of these work and return the same nodes:

while (nodes.hasNext()) { def n = nodes.nextNode(); ... }   // explicit
nodes.each { n -> ... }                                      // Groovy
for (n in nodes) { ... }                                     // for-in

Verified on Jahia 8.2.3.2 against a Digitall site: each style returned the same 130 nodes.

One difference worth knowing: nextNode() is typed, while each and for-in hand you Object, so you may want an explicit cast or @TypeChecked off when calling Jahia-specific methods on the result.

If you get "no such iterator for class"

This error is not caused by the iterator type. On 8.2.3.2 the returned iterator is iterable in Groovy in all three styles above, so a working query does not produce it.

What that leaves, in the order worth checking:

1. The session variable is not what you think. In a bare Groovy console there is no session binding unless you create one. Wrap the work in JCRTemplate as above rather than assuming a session exists.
2. Missing import. javax.jcr.query.Query must be imported for Query.JCR_SQL2 to resolve; without it the failure can surface further down the expression than where the real problem is.
3. The query returned rows, not nodes. getRows() and getNodes() are different result views; code written for one does not iterate the other.

These are places to look, not a diagnosis - the error was not reproducible from the query alone, so it depends on the surrounding script.

A note on the path

isdescendantnode() takes a path, and building it by string concatenation is how most of these scripts are written. If the value comes from anywhere outside your own code, validate it first: a path containing a quote will break the query string, and the same shape of mistake is how query injection happens.

This article was drafted with AI assistance, then reviewed and curated by Jahia Customer Support engineers before publication.