Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

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

Avatar

Level 4

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 ?

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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.

Vijayalakshmi_S_0-1632943226915.png

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

Vijayalakshmi_S_1-1632943338054.png

 

View solution in original post

1 Reply

Avatar

Correct answer by
Community Advisor

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.

Vijayalakshmi_S_0-1632943226915.png

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

Vijayalakshmi_S_1-1632943338054.png