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



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'

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)

No comments:

Post a Comment