1

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].

d33t
  • 1,143
  • 6
  • 15

1 Answers1

2

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

Imran Saeed
  • 3,414
  • 1
  • 16
  • 27
  • I'm aware with this article, but I hoped that there is an alternate solution with a query. When the repository's size is in TB, such traversal solution is a killer and very time consuming task. – d33t Jan 09 '17 at 09:43
  • Agree that this will be a killer but given that such a result cannot be obtained by a query and cannot benefit from any indexes so a traversal may be the only option. We wrote a utility to which could be termunated and continued and write a report in output for this and it was ok as it was one off. Also wrote a listener in old system to mark duplicate siblings before migration. HTH – Imran Saeed Jan 09 '17 at 09:48
  • I found this solution recently as well, thanks for helping! – d33t Jul 12 '17 at 10:49
  • It makes sense to exclude the /jcr:system path from the query. `SELECT [jcr:path] FROM [nt:base] WHERE NOT ISDESCENDANTNODE('/jcr:system') AND [jcr:path] like '%\]'` – d33t Jul 12 '17 at 10:56
  • I tested a little bit and the query seems to work only on JCR 3.x (e.g. AEM 6.X), but not against JCR 2.x, it just find nothing there (verified such nodes existense) – d33t Jul 12 '17 at 13:14
  • Yes, this will work in 3.x only. 2.x did not support `[]` notation until later version but indexes were never updated to include brackets due to performance. For 2.x the Adobe article is the viable option. Alternatively, what I did in a massive migration project was to migrate on a test platform (~TB data with pain) and then executed the queries in batches to get results. All depends on time, risk and available resources. good luck! – Imran Saeed Jul 12 '17 at 13:38