how to get substring in JCR:SQL2?

Question

Usecase that I am trying to solve is:

Find all page references of all components under /apps.

i.e. First find all pages where a component is being used, and then do this for all components under /apps.

By using the report builder tool for Adobe AEM: https://adobe-consulting-services.github.io/acs-aem-commons/features/report-builder/configuring.html

Query I am trying:

SELECT * FROM [nt:base] AS s 
WHERE [sling:resourceType] IN (SELECT path FROM [cq:Component] AS s WHERE [componentGroup] IS NOT NULL AND ISDESCENDANTNODE([/apps])) 
AND ISDESCENDANTNODE([/content])

Background: I only need to sanitize the resultset from inner query. Without sanitization, it would spit path of the form /apps/acs-commons/components/utilities/report-builder/columns/text while sling:resourceType from outer query can only accept acs-commons/components/utilities/report-builder/columns/text. So I need to strip out /apps/ from the inner query resultset path.

Here is the error message:

Caused by: java.text.ParseException: Query: SELECT * FROM [nt:base] AS s 
WHERE [sling:resourceType] IN (SELECT(*)CAST(path, AS STRING) FROM [cq:Component] AS s WHERE [componentGroup] IS NOT NULL AND ISDESCENDANTNODE([/apps])) 
AND ISDESCENDANTNODE([/content]); expected: static operand

Answer 1

I don't think you can manipulate result set using jcr sql2 syntax, stored procs are usually used to manipulate result sets akin to PL/SQL and I did not find any reference to this in jcr docs. In fact, to my knowledge jcr does not even support aggregate functions like MAX(), COUNT(), etc

Hacky way to do this -> you would probably have to execute the inner query first to retrieve all the components in /apps, modify the result set manually(stripping out /apps) and feed it to the outer query.

SELECT * FROM [nt:unstructured] AS comp
WHERE ISDESCENDANTNODE(comp, "/content/prj")
AND [sling:resourceType] IN ("prj/components/content/accordion","prj/components/content/breadcrumb")

To fasten the process, you can use text editors like notepad++ which helps you with block selection (ctrl + alt + shift and then left click mouse button and drag to select) to remove /apps, add start/end double quotes, comma and replace newline char to get it all in one line and construct the overall query.

Would be interested to know what others think and if this can be accomplished with just jcr sql2syntax.