AEM 61 - Random JCR Queries

JCR-SQL2

To execute a JCR query, try using CRXDE Lite http://localhost:4502/crx/de/index.jsp -> Tools -> Query

More queries - https://gist.github.com/floriankraft/8b3720464318cd5cd9e2


1) Get global collections

SELECT * FROM [nt:unstructured] WHERE [sling:resourceType] = 'dam/collection'

2) Get Project specific collections

SELECT * FROM [nt:unstructured] WHERE [sling:resourceType] = 'cq/gui/components/projects/admin/card/projectcard' and NAME() = '150609_belk_fisharmvntbb';

3) Get nodes (DAM Assets) with specified name

SELECT * FROM [dam:Asset] where NAME([dam:Asset]) = 'one.indd'

4) Get nodes (DAM Assets) with specified name, order by path

SELECT * FROM [dam:Asset] where NAME([dam:Asset]) = 'one.indd' ORDER BY 'jcr:path'

5) Get nodes (DAM Assets) with specified name, order by last modified date descending

SELECT * FROM [dam:Asset] where NAME([dam:Asset]) = 'one.indd' ORDER BY 'jcr:content/jcr:lastModified' desc

6) Find node with a specific property value. For example to get the node with jcr:uuid value '9074d289-faae-40b2-9400-216ed4d0fa06' (here the node type is unknown)

SELECT * FROM [nt:base] WHERE [jcr:uuid] = '9074d289-faae-40b2-9400-216ed4d0fa06'

XPath

/jcr:root/content/dam/texas//element(*, dam:Asset)[(@jcr:uuid = 'e164529c-2ea2-4925-9ad5-0c443ec57ee3')]

7) Get DAM assets in a specific path. To get the assets in folder and subfolders of /content/dam/Product/Silhouettes/Accessories/Bands/Headband

SELECT * FROM [dam:Asset] WHERE ISDESCENDANTNODE("/content/dam/Product/Silhouettes/Accessories/Bands/Headband")

8) Get the items in a user(s) inbox

SELECT * FROM [granite:InboxItem] AS s where (assignee = 'admin' or assignee = 'author') and status = "ACTIVE"

9) Get all pages with word "triangle" in components

SELECT * from [cq:Page] AS t WHERE ISDESCENDANTNODE([/content/geometrixx]) AND contains(t.*, 'triangle')

10) Get all "rep: policy"  nodes that are not of type "rep:ACL"

select * from [nt:base] as t where name(t) = 'rep:policy' AND t.[jcr:primaryType] NOT LIKE 'rep:ACL'

11) Find the config node of a sling service in CRX

select * from [sling:OsgiConfig] as s where NAME(s) = 'org.apache.sling.commons.mime.internal.MimeTypeServiceImpl'

12) Find assets (dam:Asset) with a specific metadata property containing any value (not null)

select * from [dam:Asset] as d where d.[jcr:content/metadata/uaDIO:division] IS NOT NULL

13) Find nodes modified after a specified date (This query is not functioning as expected in oak, may be because jcr:lastModified is not always being stored as date)

SELECT * FROM [nt:base]  WHERE[jcr:lastModified] > cast('2015-07-25T00:00:00.000Z' as date) order by [jcr:lastModified] desc

14) Find nodes with property cq:tags containing values experience-aem:english or stockphotography:business/businesspeople (cq:tags is a multi-valued property, so query checks if each value is equal to the ones specified in query)

SELECT * from [nt:base] AS t WHERE ISDESCENDANTNODE([/content]) AND ( t.[cq:tags] = 'experience-aem:english' 
OR t.[cq:tags] = 'stockphotography:business/businesspeople')

15) Sample union query, returning dam assets with metadata property eaem:option1='One' or property eaem:option2='Two'

select [jcr:path], [jcr:score], * from [dam:Asset] as a where isdescendantnode(a, '/content/dam') and [jcr:content/metadata/eaem:option1] = 'One'
union select [jcr:path], [jcr:score], * from [dam:Asset] as a where isdescendantnode(a, '/content/dam') and [jcr:content/metadata/eaem:option2] = 'Two'

16) Find pages under a path eg. /content/mobileapps/ua-mens-running/collections, where name of a page is like something eg. global_FW16_Running_Apparel_

SELECT * FROM [cq:Page] WHERE ISDESCENDANTNODE('/content/mobileapps/ua-mens-running/collections') AND NAME([cq:Page]) like 'global_FW16_Running_Apparel_%'

17) Finding the resource with a sling vanity path eg. /assetdetails

SELECT * FROM [sling:VanityPath] WHERE [sling:vanityPath] = '/assetdetails'

18) Get the list of available otb asset metadata fields in 64

SELECT * FROM [nt:unstructured]  AS t WHERE ISDESCENDANTNODE('/libs/dam/content/schemaeditors') AND t.[name] like './%'

19) Check if a thumbnail rendition exists

select * from [dam:Asset] as d where d.[jcr:content/renditions/cq5dam.thumbnail.48.48.png/jcr:primaryType] not like 'nt:file'

20) Find assets with a tag

/jcr:root/content/dam/psds//element(*, dam:Asset)[ jcr:like(jcr:content/metadata/@cq:tags,  'we-retail:activity') ] order by @jcr:score

21) Find asset paths used in a page

SELECT * from [nt:unstructured] AS t WHERE ISDESCENDANTNODE([/content/eaem/us/en/home/jcr:content]) AND t.[fileReference] LIKE '%/content/dam/eaem/%' 

UNION 

SELECT * from [nt:unstructured] AS t WHERE ISDESCENDANTNODE([/content/eaem/us/en/home/jcr:content]) AND t.[src] LIKE '%/content/dam/eaem/%'

22) XPath query for checking if the name matches....

/jcr:root/content/dam//element(*, dam:Asset)[(fn:name()='256551_E_DGT_Invested_Beef_Print ad_3-5x8.indd')]

/jcr:root/content/dam//element(*, dam:Asset)[jcr:like(fn:name(),'256551_E_DGT_Invested_Beef_Print ad_3-5x8.indd')]

23) Find all published  content on that particular day 

select [jcr:path], [jcr:score], * from [cq:Page] as a where [jcr:content/cq:lastReplicated] > cast('2021-12-10T00:01:00.000Z' as date) and [jcr:content/cq:lastReplicated] < cast('2021-12-11T23:59:00.000Z' as date) and [jcr:content/cq:lastReplicationAction] = 'Activate' and isdescendantnode(a, '/content')

24) Find assets with missing metadata node

select * from [dam:Asset] as d where isdescendantnode('/content/dam/test') AND d.[jcr:content/metadata/jcr:primaryType] not like 'nt:unstructured'

25)