JCR Query Cheat Sheet

This is a collection of mini-recipes for doing JCR queries. Please add your own!

	SQL (deprecated in JCR 2.0)	XPath (deprecated in JCR 2.0)	SQL2
All pages	select * from mgnl:content	//element(*, mgnl:content)	select * from [mgnl:page]
Pages with "News" in the title	select * from mgnl:content where title like '%News%'	//element(*, mgnl:content)[jcr:like(@title, '%News%')]	select * from [mgnl:page] where title like '%News%'
Pages where the title exactly matches "News" (case sensitive)	select * from mgnl:content where title like 'News'	//element(*, mgnl:content)[@title = 'News']	select * from [mgnl:page] where title like 'News'
STK pages that have a header image	select * from mgnl:content where image is not null	//element(*, mgnl:content)[@image]	select * from [mgnl:page] where image is not null
Instances of a "Teaser" paragraph	select * from nt:base where mgnl:template = 'stkTeaser'	//*[@mgnl:template = 'stkTeaser']	select * from [nt:base] where [mgnl:template] = 'standard-templating-kit:components/teasers/stkTeaser'
Available paragraph types	select * from nt:base where jcr:path like '/modules/%/paragraphs/%' and type is not null	/jcr:root/modules[1]///paragraphs[1]//[@type]
User with email 'eric@example.com'	select * from mgnl:user where email = 'eric@example.com'	//element(*, mgnl:user)[@email = 'eric@example.com']	select * from [mgnl:user] where email = 'eric@example.com'
Pages that have the word "component"	SELECT * from nt:base WHERE jcr:path like '/ftl-sample-site%' AND contains(*, 'component') AND (jcr:primaryType = 'mgnl:page' OR jcr:primaryType = 'mgnl:area' OR jcr:primaryType = 'mgnl:component') order by jcr:path		SELECT * from [nt:base] AS t WHERE ISDESCENDANTNODE('/ftl-sample-site') AND contains(t.*, 'component')
Template folders in module configuration	select * from mgnl:content where jcr:path like '/modules/%/templates'		select * from [mgnl:content] as t where ISDESCENDANTNODE('/modules') and name(t) = 'templates'
Modules that provide commands	select * from nt:base where jcr:path like '/modules/%/commands'		select * from [mgnl:content] as t where ISDESCENDANTNODE('/modules') and name(t) = 'commands'
All pages with a specific template ordered by title			SELECT p.* FROM [nt:base] AS p WHERE [mgnl:template] = 'xxx:pages/jobs' order by p.[title] asc
Pages under given path with given template	select * from nt:base where jcr:path like '/demo-project/%' AND mgnl:template = 'standard-templating-kit:stkNews'		SELECT parent.* FROM [mgnl:page] AS parent INNER JOIN [mgnl:metaData] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, '/demo-project') AND child.[mgnl:template] = 'standard-templating-kit:stkNews' ** When using this query, one need to get results via getRows() instead of getNodes() since queries w/ joins can eventually return multiple different node types.
Pages under given path with given template and category ordered by date		/jcr:root/demo-project//element(*, mgnl:metaData)[@mgnl:template = 'standard-templating-kit:pages/stkArticle']/..[@categories = 'ab9437db-ab2c-4df5-bb41-87e55409e8e1'] order by @date
Search a Node with a certain UUID	select * from nt:base where jcr:uuid = '7fd401be-cada-4634-93fa-88069f46297b'		SELECT * FROM [nt:base] WHERE [jcr:uuid] = '7fd401be-cada-4634-93fa-88069f46297b'
Search case insensitive	select * from nt:base where lower(name) like 'name_in_lowercase'		select * from [nt:base] where lower(name) like 'name_in_lowercase'
Search demo-project pages created in given time frame			select * from [mgnl:page] where ISDESCENDANTNODE('/demo-project/') and [jcr:created] > cast('2010-01-01T00:00:00.000+02:00' as date) and [jcr:created] < cast('2014-09-30T23:59:59.000+02:00' as date)
Pages in 'demo-project' which using a specific template ('stkSection' for example) and has the content just been modified by 'eric'		/jcr:root/demo-project/*[mgnl:template='standard-templating-kit:pages/stkSection']/*/content[mgnl:lastModifiedBy='eric']/../..
Get all nodes which have a property 'date' which is not empty and this date starts at least 1 second after current midnight. Useful for events.			SELECT p.* FROM [nt:base] AS p WHERE ISDESCENDANTNODE('/') AND (p.[date] <> '' AND p.[date] > CAST('2015-11-30T00:00:01.000Z' AS DATE)) order by p.[date] asc

Source: https://wiki.magnolia-cms.com/display/WIKI/JCR+Query+Cheat+Sheet