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 Digital Experience platforms. This cheat sheets provides many examples of syntaxes that can be reused and combiled to create queries.

1 Querying nodetypes

Select all nodes, no matter their type

Selecting nt:base is not a recommended approach since it will grab all system nodes and will be 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 node returned. Please 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 to query files, pages and editorial content at the same time.

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')

5 Querying templates

Select all the pages with a specific template

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

6 Querying specific types

Select all the news ordered by date

The DESC keyword at the end of the query will reverse 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 weakrefence is used 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'

7 Join clauses

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