How to search duplicate node by JCR-SQL2

[jiaBai]

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.

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)

[Dipti_Chauhan · Accepted solution]

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

[jiaBai]

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.