JCR-SQL2 Query to Return All Immediate Folders from a Path

Question

I would like to get all the immediate folders from a given path. For example, I would like to query the 6 immediate folders under we-retail/en:

I found a similar post here: https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/aem-querybuilder-how-to-return-only-the-folders-under-a-path/m-p/269081 but it is using X-Path.
Here is the query for XPATH:

type=nt:base

path=/content/dam/we-retail/en

property=jcr:primaryType

property.1_value=sling:OrderedFolder

property.2_value=sling:Folder

path.flat=true

p.hits=selective

p.properties=jcr:path

Does anyone know what would be the query for JCR SQL2? Thanks!

Ritesh_Mittal · Accepted Answer

Hi @aemuser2345 ,

You can use below SQL2 query to search the folders-

SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/dam/we-retail/en]) and [jcr:primaryType] = 'sling:OrderedFolder'

And, can limit the results by setting the query limit-

query.setLimit(6);

Thanks,

Ritesh Mittal

arunpatidar · Answer

You can try something likeSELECT * FROM [jcr:primaryType] AS s WHERE ISCHILDNODE([/content/dam/we-retail/en])  ISCHILDNODE will give you only children nodes

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded