JCR-SQL2 Query cheat sheet

JCR-SQL2 Query cheat sheet
The JCR SQL2 query language is defined by the JCR 2.0 specification and provides a powerful API to query nodes on Jahia platforms. These cheat sheets provide syntax examples of you can reuse and combine to create queries.

Querying node types
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 node type. 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 querying 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 taking the case into account
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 is performing 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 weak reference pointing to a given node
A weak reference 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'

Citation:

Author: Jahia Academy

Blog Reference URL: https://academy.jahia.com/documentation/developer/jahia/advanced-guides/search/jcrsql2-query-cheat-sheet