How to exclude node in aem using Query builder and JCR-SQL2? | Community
Skip to main content
Anderson_Hamer
Anderson_Hamer
Level 4
September 29, 2021
Solved

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

  • September 29, 2021
  • 1 reply
  • 3255 views

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 ?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Vijayalakshmi_S

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)

 

1 reply

Vijayalakshmi_S
Vijayalakshmi_SAccepted solution
Level 10
September 29, 2021

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
    Anderson_HamerAuthor
    Level 4
    September 29, 2021

    Thank you. notexists operation works!

      Terms & ConditionsAccessibility statement

      Loading footer...