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

CQ5 XPath Queries (Content)

Nodes with a certain attribute      
//element(*)[jcr:content/@includePageInTools='true']

News without PubDate      
//*[@cq:template="/apps/sampleproject/templates/newspage" and not(@pubdate)]

Pages without UUID      
/jcr:root/content//element(*,cq:PageContent)[not(@uuid)]

Find Pages with component     
//element(*)[@sling:resourceType='/apps/sampleproject/components/news/ts_news_filter_c1']

Find all news personalization nodes that are personalized for property personalization=USERS%
/jcr:root/content/news//element(*)[@sling:resourceType='sampleproject/components/par/newspersonalizationbar' and jcr:like(@personalization, 'USERS%')] 

All nodes personalization nodes with personalization containing today (slow)
//element(*, cq:Page)//element(*,nt:unstructured)[jcr:contains(@personalization,'*today*')]";

All newspersonalization nodes with personalization property containing today (fast)
//element(*,nt:unstructured)[jcr:contains(.,'newspersonalization') AND jcr:contains(@personalization,'today')]

Get all news articles with city property equals moscow and date newer then 2007 (slow) .. better user a generic timestamp here
//element(*)[@sling:resourceType='sampleproject/components/pages/newsarticle' and news/@city='Moscow'][news/@dateAndTime >= xs:dateTime('2007-01-01T00:00:00.000Z')]

CQ5 XPath Queries (Users, Groups and ACLs)

All users without a password property
//element(*,rep:User)[not(@rep:password)] 
//element(*)[jcr:primaryType='rep:User' and not(@rep:password)] 

Users without country
/jcr:root/home/users//element(*)[@sling:resourceType='cq/security/components/profile' and not(@country)]

Users with empty country
/jcr:root/home/users//element(*)[@sling:resourceType='cq/security/components/profile' and @country='']

Find all ACE entries 
/jcr:root//element(*,rep:ACE) 

// All allow acls nodes for group (principal) administrators
/jcr:root//element(*,rep:GrantACE)[@rep:principalName='administrators']

// All deny acls nodes for group (principal) administrators
/jcr:root//element(*,rep:DenyACE)[@rep:principalName='administrators']

CQ5 XPath Queries (Workflow)

Running workflows matching a path      
//*[@status="RUNNING"]/data/payload[@path="/content/geometrixx/de"]

All workflows having a certain cq:tags property in the metadata and of a specific model
/jcr:root//*[modelId='/etc/workflow/models/acceptance-workflow/jcr:content/model']/metaData[@cq:tags='workflow-package:acceptance-test-6']