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

[aemUser2345]

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-re... 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 solution]

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]

You can try something like

SELECT * FROM [jcr:primaryType] AS s WHERE ISCHILDNODE([/content/dam/we-retail/en]) 

ISCHILDNODE will give you only children nodes