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?

Anderson_Hamer
Level 4
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
Vijayalakshmi_S
Correct answer by
Community Advisor
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

2 Replies
Vijayalakshmi_S
Correct answer by
Community Advisor
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