A collection of AEM related JCR queries in the SQL2 query language. Some of these queries might be project specific but are a nice blueprint for similar requirements.

AEM SQL2 Queries

# Find all custom image components by resource type below /content 
SELECT * FROM [nt:unstructured] as r WHERE ISDESCENDANTNODE(r,'/content') AND [sling:resourceType] = 'project/components/par/image'

SELECT * FROM [nt:unstructured] as r WHERE ISDESCENDANTNODE(r,'/content') AND [sling:resourceType] = 'project/components/par/image' AND [fileReference] IS NOT NULL

# Find all users below a certain folder 
SELECT * FROM [rep:User] as r WHERE ISDESCENDANTNODE(r,'/home/users/community/project')

SELECT * FROM [nt:unstructured] as r WHERE ISDESCENDANTNODE(r,'/home/users/community') AND [sling:resourceType] = 'cq/security/components/profile' AND [registrationDate] IS NOT NULL

SELECT * FROM [nt:unstructured] as r WHERE ISDESCENDANTNODE(r,'/home/users/community') AND oauthid LIKE  "oauthSubjectId_%"