How to Query Jackrabbit for same name siblings

Question

Is it possible to find the same-name siblings (SNS) using JCR-SQL2, JCR-SQL or QueryBuilder in Adobe CQ5/Adobe Experience Manager. I'm trying to match those nodes with a query having the following criteria without having to traverse the whole repository (slow and long running operation):

if(node.getIndex() > 1) {
  // this node is matching the SNS criteria
}

SNS are defined as follows:

/a/b/c
/a/b/c[2]
/a/b/c[3]

/a/b[2]/c[2]
/a/b/c[3]

/a/d/f
/a/d/f[2]

So the result of the query should include /a/b/c[2], /a/b/c[3], /a/b[2]/c[2], /a/b/c[3], /a/d/f[2].

Answer 1

Adobe published a helpful article for this at:

https://helpx.adobe.com/experience-manager/kb/find-sns-nodes.html

EDIT: One query for this may be as below:

SELECT [jcr:path] FROM [nt:base] WHERE ISDESCENDANTNODE('/') AND [jcr:path] like '%\]'

The idea is that oak queries will be able to find indexed nodes that were migrated via SNS resolution logic. These names will contain ] in their names (paths for URI) which will be selectable via above query.

Use this query with caution as there are a lot of system nodes OOTB that have ] in the name and this is by design.

You can change [nt:base] to other relevant oak index for better filtering.

HTH