Some useful JCR queries (XPATH, SQL2) for AEM/CQ development.

JcrQueryLibrary.md

SQL2

All nodes with a specific name

SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/search/in/path")
AND NAME() = "nodeName"

All pages below content path

SELECT * FROM [cq:Page] AS page
WHERE ISDESCENDANTNODE(page ,"/search/in/path")

All components of a specific Resource Type

SELECT * FROM [nt:unstructured] AS comp
WHERE ISDESCENDANTNODE(comp, "/search/in/path")
AND [sling:resourceType] = "componentType"

All nodes where a property contains some String

SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/search/in/path")
AND CONTAINS([propertyName], "someString")

All nodes where a property is not null and not empty

SELECT * FROM [nt:unstructured] AS node
WHERE node.[propertyName] IS NOT NULL AND node.[propertyName] <> ""

All nodes where a property is null or empty

SELECT * FROM [nt:unstructured] AS node
WHERE (node.[propertyName] = "" OR node.[propertyName] IS NULL)

All nodes where a property is of type Date (also possible: String, Long, Boolean and more)

SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/search/in/path")
AND PROPERTY(node.[propertyName], "DATE") LIKE "%"

Date property of child node is greater than

SELECT page.* FROM [cq:Page] AS page
INNER JOIN [cq:PageContent] AS jcrcontent ON ISCHILDNODE(jcrcontent, page)
WHERE ISDESCENDANTNODE(page, "/content/path/to/page")
AND jcrcontent.[cq:lastModified] >= CAST("2019-01-01T00:00:00.000Z" AS DATE)

Every page with specific name

SELECT * FROM [cq:Page] AS page
WHERE ISDESCENDANTNODE(page, "/search/in/path")
AND name() = "pageName"

Every page with specific name, that has a child node with a specific name

SELECT * FROM [cq:Page] AS page
INNER JOIN [nt:base] AS childnode ON ISCHILDNODE(childnode, page)
WHERE ISDESCENDANTNODE(page, "/search/in/path")
AND name(page) = "pageName"
AND name(childnode) = "nodeName"

XPATH

Property not empty

/jcr:root/content/path/to/page//nodeName[@propertyName != ""]

Specific property contains

/jcr:root/content/path/to/page//nodeName[jcr:contains(@propertyName,'someValue')]

Any property contains

/jcr:root/content/path/to/page//*[jcr:contains(., 'someValue')]

AND operation (for more than one property)

/jcr:root/content/path/to/page//*[@firstPropertyName = "someValue", @secondPropertyName != "anotherValue"]

Get all jcr:content nodes that

• have a specific resourceType
• have any property that contains a specific (text-) value
• have a "grandchild" node, which has a specific resourceType
• have a "grandchild" node, which has a property called "date" greater than "2012-06-01" and less than "2016-07-01" and order the result by the date property in descending order.

/jcr:root/content/path/to/node//element(*,cq:PageContent)[@sling:resourceType="someType" and jcr:contains(., "someValue") and */*/@sling:resourceType="anotherType" and */*/@date >= xs:dateTime("2012-06-01T00:00:00.000+02:00") and */*/@date <= xs:dateTime("2016-07-01T00:00:00.000+02:00")] order by pathto/grandchild/@date descending

+1

+!

+1
Und lieben Gruß :-)

Java is failing because it cannot parse string into a date. Can you tell me what the syntax would be if I am trying to find a record that doesn't parse into a date field?
AND [ExpirationDate] .....

SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/search/in/path")
AND PROPERTY(node.[propertyName], "DATE") LIKE "%"

whatever results i am getting with this query, same i am not getting through querybuilder.
propertyName i can given DATE or LONG in sql2 but not in xpath. Can anyone assist on this?

How to get tags of pages under a parent page through SQL query in aem

How to get tags of pages under a parent page through SQL query in aem

See if this link helps https://hashimkhan.in/aem-adobecq5-code-templates/groovy-script/

any idea how to get a specific property of the parent of an node?
i already tried:
${currentNode}/nodePath/../
Or is this even possible inside an definition of an aem dialog (xml, apache sling)

Have you tried ${currentNode}/../@myProperty ?

My post at https://itgumby.github.io/blog/2018/jcr-xpath-reference.html was aimed at JCR XPath queries but admitted that I tended to stay with SQL2 inside AEM.

When building an XPath query, you are targeting specific nodes. However you can match properties of those nodes OR their parents. For instance //*[@nodeProp='value1' and ../@parentProp='value2'] should work in the query. However, you could also build the query like //*[@parentProp='value2']/*[@nodeProp='value1']

Each implementation of the query results are different. The result set is typically a NodeIterator instead of a table of properties. But some implementations allow a visual table of specific properties. I have NOT had any success with those returning tables of properites of different level nodes - just properties directly on the node(s) matched by the query.

THX!