Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
JCR-SQL2 examples (AEM6)
Find all videos below a specific DAM path
-----------------------------------------
SELECT * FROM [dam:Asset] AS asset
WHERE ISDESCENDANTNODE(asset, '/content/dam')
AND asset.[jcr:content/metadata/dc:format] LIKE 'video/%'
Find all pages with a specific template and an alias
----------------------------------------------------
SELECT * FROM [cq:Page] AS page
WHERE ISDESCENDANTNODE(page, '/content')
AND page.[jcr:content/cq:template] IN(
'/apps/foo/templates/entrypage',
'/apps/foo/templates/contentpage',
'/apps/foo/templates/configpage' )
AND page.[jcr:content/sling:alias] IS NOT NULL
Find all pages with a specific template and an alias
*** same as before but with a (slower) inner join ***
-----------------------------------------------------
SELECT * FROM [cq:Page] AS page
INNER JOIN [cq:PageContent] AS content ON ISCHILDNODE(content, page)
WHERE ISDESCENDANTNODE(page, '/content')
AND content.[cq:template] IN(
'/apps/foo/templates/entrypage',
'/apps/foo/templates/contentpage',
'/apps/foo/templates/configpage' )
AND content.[sling:alias] IS NOT NULL
Find all livecopy nodes by node name within a certain path
----------------------------------------------------------
SELECT * FROM [nt:base] AS node
WHERE ISDESCENDANTNODE(node, '/content')
AND NAME(node) = 'cq:LiveSyncConfig'
Find nodes filterd by multiple properties
-----------------------------------------
SELECT * FROM [cq:Page] AS page
WHERE ISDESCENDANTNODE(page, '/content')
AND page.[jcr:content/sling:resourceType] = 'apps/foo/components/pages/entrypage'
AND page.[jcr:content/cq:template] = 'apps/foo/templates/entrypage'
AND page.[jcr:content/jcr:title] LIKE 'Welcome%'
Find user preferences by authoring mode
---------------------------------------
SELECT * FROM [nt:base] AS pref
WHERE ISDESCENDANTNODE(pref, '/home/users')
AND pref.[sling:resourceType] = 'cq:Preferences'
AND (
pref.[authoringMode] IS NULL
OR
NOT pref.[authoringMode] = 'CLASSIC'
)
Find assets modified after a specific date and time ordered by date
-------------------------------------------------------------------
SELECT * FROM [dam:Asset] AS asset
WHERE ISDESCENDANTNODE(asset, '/content/dam')
AND asset.[jcr:content/jcr:lastModified] >= CAST('2018-09-16T21:04:18.147+01:00' AS DATE)
ORDER BY asset.[jcr:content/jcr:lastModified] DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.