JCR-SQL2 Query cheat sheet

October 8, 2024

The JCR SQL2 query language is defined by the JCR 2.0 specification and provides a powerful API to query nodes on Jahia platforms. This cheat sheet provides syntax examples that you can use to create queries.

Querying nodetypes

Select all nodes, no matter their type

Selecting nt:base is not a recommended approach since it grabs all system nodes and is significantly slower than querying any other nodetype. This selector must only be used when no other option is available, and must be used in conjunction with an ISDESCENDANTNODE clause to limit the number of nodes returned. Consider querying jmix:searchable as an alternative to nt:base when possible.

SELECT * FROM [nt:base] as base

Select all editable nodes

To avoid querying nt:base too often, jmix:searchable was introduced. It allows you to query files, pages, and editorial content at the same time. 

SELECT * FROM [jmix:searchable] as searchable

Select nodes of a given type

SELECT * FROM [jnt:page]
SELECT * FROM [jnt:file]
SELECT * FROM [jnt:user]

Querying paths

Select nodes under a given path

Restricting the returned nodes based on their path is a key aspect of performance improvement when querying. Limit the number of searched nodes to a maximum to reduce query execution time.

SELECT * FROM [jnt:content] As node WHERE ISDESCENDANTNODE (node, '/sites/digitall')

Select nodes under multiple paths

SELECT * FROM [jnt:content] As node WHERE ISDESCENDANTNODE (node, '/sites/digitall') or ISDESCENDANTNODE (node, '/sites/ACME-SPACE')

Querying strings

Select nodes where a property exactly matches a string

SELECT * FROM [jnt:content] As node WHERE node.[jcr:title] = 'My Title'

Select nodes where a property partially matches a string

SELECT * FROM [jnt:content] As node WHERE node.[jcr:title] like '%My Title%'

Select all pages where the title matches a string without considering the case

select * from [jnt:page] as node where lower(node.[jcr:title]) = 'home'
select * from [jnt:page] as node where upper(node.[jcr:title]) = 'HOME'

Select all sites where the site name has a length of 8

SELECT * FROM [jnt:virtualsite] as site where LENGTH(site.[j:nodename]) = 8

Querying properties

Select all pages with a null title

SELECT * FROM [jnt:page] As page WHERE page.[jcr:title] is not null

Select all the content nodes of two different types

For this query to work, the main selector (jnt:content) must be inherited by all searched types. Most of the time, jnt:content or jmix:searchable is the right selector to use.

SELECT * FROM [jnt:content] As node WHERE node.[jcr:primaryType] = 'jnt:bigText' or node.[jcr:primaryType] = 'jnt:article'

Select all the content nodes that have a property containing the String "digital". This query performs a full text search.

SELECT * FROM [jnt:content] As node WHERE contains(node.*, 'digital')

Querying templates

Select all the pages with a specific template

SELECT * FROM [jnt:page] As page WHERE page.[j:templateName] = 'home'

Querying specific types

Select all the news ordered by date

The DESC keyword at the end of the query reverses the order of the result set.

SELECT * FROM [jnt:page] As page order by page.[j:created]
SELECT * FROM [jnt:page] As page order by page.[j:published] DESC
SELECT * FROM [jnt:page] As page order by page.[j:lastModified] DESC
SELECT * FROM [jnt:page] As page order by page.[j:lastPublished]

Select all the pages published after January 1st, 2017

select * from [jnt:page] as page where page.[j:published] > CAST('2017-01-01T00:00:01.000Z' AS DATE)

Select all nodes based on a boolean property

select * from [jnt:page] as page where page.[j:published] > CAST('true' AS BOOLEAN)

Select all the image reference nodes which have a weakreference pointing to a given node

A weakreference the same thing as a String property that contains the UUID of the referenced node. 

select * from [jnt:imageReferenceLink] as imageLink where imageLink.[j:node] = '70846435-5575-40f5-9381-491c167f8803'

Join clauses

Select all the file reference nodes that reference a given file with a JOIN clause

select * from [jnt:imageReferenceLink] as imageLink inner join [jnt:file] as file on imageLink.[j:node] = file.[jcr:uuid] where imageLink.[j:node] = '70846435-5575-40f5-9381-491c167f8803'

Specific queries

Select all users that are part of a group

SELECT member.* FROM [jnt:member] AS member WHERE (NOT member.[j:isExternalProviderRoot] IS NOT NULL) AND ISDESCENDANTNODE(member, [‘/groups/test’])

Limit the result set to a specific number of nodes

It is not possible to set a limit in the query string itself, but the JahiaQueryObjectModelImpl Java object does offer a setLimit() method.

Query a node based on its UUID

Querying a node based on its identifier is usually not a good idea from a performance standpoint. Please use JCRSessionWrapper's getNodeByIdentifier() instead.

Query a node based on a parent/child relationship

SELECT parent.* FROM [jnt:page] AS parent INNER JOIN [jnt:acl] AS child ON ISCHILDNODE(child,parent)

Select all png files uploaded by authors

SELECT * FROM [jnt:file] WHERE [j:nodename] LIKE '%.png'

Select all content nodes created by Irina

SELECT * FROM [jnt:content] as content WHERE content.[jcr:createdBy] = 'Irina'