How to exclude node in aem using Query builder and JCR-SQL2?

[Anderson_Hamer]

I am trying to get a page that do not have jcr:node node.

Query builder

Tried with Query builder but it is not excluding the node

path=/content/en_GB/about-us
property=jcr:primaryType
property.value=cq:PageContent
nodeExists.notexists=jcr:content
p.limit=-1

JCR-SQL2

Tried to create a SQL for the same but query is giving exception while running,

SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s, '/content/en_GB/about-us') NOT (jcr:content)

Can you please help me to understand why above sql query is not working and why query builder is not excluding jcr:content node and ?

[Vijayalakshmi_S · Accepted solution]

Hi @Anderson_Hamer,

Please find below for the query builder predicate and JCR-SQL2 query for your use case

QueryBuilder Predicate (Makes use of ACS NodeExists Predicate)

path=/content/en_GB/about-us
type=cq:Page
nodeExists.notexists=jcr:content
p.limit=-1

JCR-SQL2:

select a.* from [cq:Page] as a left outer join [cq:PageContent] as b on ischildnode(b, a)  where isdescendantnode(a, '/content/en_GB/about-us')  and b.[jcr:primaryType] is null

I have reproduced your scenario(return all pages which doesn't has jcr:content node) in my local and here is the screenshot of results for your reference.

JCR-SQL2: (no jcr:content on highlighted pages)

[Anderson_Hamer]

Thank you. notexists operation works!