JCR SQL2 query issue

[PriyankaKh4]

Hi team,

I am running a JCR SQL2 query to fetch all sling:Folder nodes under a specific path in AEM, but it’s returning zero results, even though I can see folders under that path in CRXDE.

Here is the query:

SELECT * FROM [sling:Folder] AS s WHERE ISDESCENDANTNODE([/content/ohip])

Also tried with cq:Page like:

SELECT * FROM [cq:Page] AS p WHERE ISDESCENDANTNODE([/content/ohip])

I confirmed that /content/ohip exists and has pages/folders underneath. Why is this query not returning anything?

Thanks in advance.

[SantoshSai · Accepted solution]

Hi @PriyankaKh4,

Try this:

SELECT * FROM [cq:Page] AS p 
WHERE ISDESCENDANTNODE(p, '/content/ohip')

AEM stores content and how JCR primary types work.

• Nodes like /content/ohip/home may appear as folders/pages, but their primary types are often nt:unstructured, not sling:Folder or cq:Page.

• If you’re looking for actual pages, you need to query for cq:Page nodes specifically - and only the page root nodes (not jcr:content) have that type.

OR

To get all folders regardless of node type (like nt:unstructured, sling:OrderedFolder, etc.), use a property filter or switch to XPath if you're unsure of types.

References: 

• https://experienceleague.adobe.com/en/docs/experience-manager-65/content/implementing/developing/pla...
• https://jackrabbit.apache.org/oak/docs/query/grammar-sql2.html

[MukeshYadav_]

Hi @PriyankaKh4 ,

Are you trying to fthc folder inside dam, then you can use below query, Replace the project-name with your project

SELECT * FROM [sling:Folder] AS folder
WHERE ISDESCENDANTNODE(folder, '/content/dam/project-name')

or if you want query builder query

path=/content/dam/projectname
type=sling:Folder
1_property=jcr:primaryType
1_property.value=sling:Folder
p.limit=-1

Note: Inside /content/project , it will return zero result as at this place pages only exist.
If there are folders cross check their jcr:primaryType and replace accordingly int he above query
Thanks