JCR-SQL2 examples (AEM6)

examples.sql2

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