Expand my Community achievements bar.

SOLVED

How to search duplicate node by JCR-SQL2

Avatar

Level 4

Hi Guys,

 

I have issue that need to retrieve duplicate node name from two node's subset via JCR-Sql2 as below attachment.

The search result should be /aa/cc and /aa/dd .

 

But I always got below ParseException, does anyone know how to correct this Sql clause and make it working correct?

Or if you know the correct JCR-Sql2 query.

Thanks in advance.

jiaBai_0-1625730373375.png

 

SELECT * FROM [nt:base] AS a inner join [nt:base] as b on a.[jcr:primaryType] = b.[jcr:primaryType] where ISDESCENDANTNODE(a, [/content/aa]) and ISDESCENDANTNODE(b, [/content/bb])
and name(a) = name(b)

jiaBai_1-1625730527890.png

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @jiaBai 

   In this query , Static operand is expected at right hand side(which is name(b)). I am also curios to know if we can compare these two nodes using query,

If your usecase is to do some cleanup then I would suggest you to write groovy or small servlet and iterate the ndoe and do comparison on JCR property level.

 

Thanks

Dipti

View solution in original post

2 Replies

Avatar

Correct answer by
Community Advisor

Hi @jiaBai 

   In this query , Static operand is expected at right hand side(which is name(b)). I am also curios to know if we can compare these two nodes using query,

If your usecase is to do some cleanup then I would suggest you to write groovy or small servlet and iterate the ndoe and do comparison on JCR property level.

 

Thanks

Dipti

Avatar

Level 4

Hi @Dipti_Chauhan,

 

Our use case only can search via JCR-Sql2 from CRX console, it's due to I have no permission to deploy bundle code in AEM.

Otherwise, to fetch duplicate node by sql that is more simply.

 

Please let me know if you know the correct Query syntax.

Thanks very much.