JCR SQL2 Query - find the parent nodes that do not contain children of a type

[NitroHazeDev]

The structure i have is  a@b.com/yyyy/mm/dd format with nt:unstructured nodes under the date node.

I am trying to get the path at date level which in our case is "25" in case-2 and "15" in case-1, and get "15" since it has no nt:unstructured nodes under it.

What i get is:

Case 1 & case 2 responses: 

path to node year node- 2021,  path to date node -15 and path to month node -3

Please let me know if I am missing something.

Query 

SELECT folder.* FROM [sling:Folder] AS folder
LEFT OUTER JOIN [nt:unstructured] AS childnode ON ISCHILDNODE(childnode, folder)
WHERE ISDESCENDANTNODE(folder, "/var/test/links/a@b.com")
AND childnode.[jcr:primaryType] IS NULL
AND childnode.[sling:resourceType] IS NULL

case1- no nt:unstructured node under  node "15"

a@b.com   ( sling:Folder)

   |____2021 ( sling:Folder)

              |____03 ( sling:Folder)

                        |___15   ( sling:Folder)

case2-  nt:unstructured node under  node "25"

a@b.com   ( sling:Folder)

   |____2022 ( sling:Folder)

              |____04 ( sling:Folder)

                        |___25   ( sling:Folder)

                                |____nt:unstructured, sling:resourceType = "sling/collection"

                                                    |______________________sling:members(nt:unstructured)

                                                                                                          |_________test.jpg(nt:unstructured)

[Anish-Sinha]

Hi @NitroHazeDev ,
By looking at the query, it looks like you might be getting all the folder path in the response in addition to 15. The reason being, here you are considering only nt:unstructured type into childnode variable. When you do "AND childnode.[jcr:primaryType] IS NULL" this will only consider nt:unstructure node and not sling:Folder type. So your result will not consider sling folder type to check your query and have extra paths which is something like:

/a@b.com/2022/04

/a@b.com/2022

/a@b.com/2021

/a@b.com/2021/03

/a@b.com/2021/03/15

[NitroHazeDev]

@Anish-Sinha  This brings in all sling folders in short yes and i want the node where date is listed which is 15.

So in case 1 it prints:

2021, 2021/03, 2021/03/15

In case2:

2022, 2022/04 and does not print the next node that is the date  since it perhaps checks that there is no nt:unstructured underneath it? Is there a way to fine tune it?

[Anish-Sinha · Accepted solution]

SELECT folder.* FROM [sling:Folder] AS folder LEFT OUTER JOIN [nt:unstructured] AS childnode ON ISCHILDNODE(childnode, folder) WHERE ISDESCENDANTNODE(folder, "/var/test/links/a@b.com") and folder.[jcr:path] LIKE '/var/test/links/%/%/%/%' AND childnode.[jcr:primaryType] IS NULL AND childnode.[sling:resourceType] IS NULL

Check if this works. Assuming:

1. your path is constant

2. if you run on the root path, which in this case is /var/test/links/a@b.com , you should get all empty folder path until date.

[NitroHazeDev]

This worked like a charm for a constant structure. Adding and  folder.[jcr:path] LIKE ''/var/test/links/%/%/%/%"    worked. Thanks a lot @Anish-Sinha 

[NitroHazeDev]

@Anish-Sinha  this looks like it needs an index .. per the logs .. wanted to also know if i were to achieve using query builder with node depth, how would that be?
type = sling:Folder
path = /var/test/links/a@b.com
property = sling:resourceType

property.operation = not
property.depth =4
p.limit =-1

[lukasz-m]

Hi @NitroHazeDev, assuming that your sling folders structure under /var/test/links is consistence and will have always the same depth, and if you will never have direct sling:Folder under node representing day, then you could try use below query.

SELECT * FROM [sling:Folder] AS folder WHERE ISDESCENDANTNODE([/var/test/links]) and folder.[jcr:path] LIKE '/var/test/links/%/%/%/%'

[NitroHazeDev]

@lukasz-m  thanks and yes your understanding is right.. however this leads to a 100000 traversal , the query traversal limit set on the machine. The query i executed (initial email) did not throw any. Also, wouldn't this retrieve the sling:members and other asset node that is nt:unstructured?

[lukasz-m]

You are right, I think you will need to create additional index, which could look like this:

<?xml version="1.0" encoding="UTF-8"?><jcr:root xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:sling="http://sling.apache.org/jcr/sling/1.0">
  <myIndex compatVersion="{Long}2" async="async" jcr:primaryType="oak:QueryIndexDefinition" evaluatePathRestrictions="{Boolean}true" type="lucene">
    <indexRules jcr:primaryType="nt:unstructured">
      <sling:Folder jcr:primaryType="nt:unstructured">
        <properties jcr:primaryType="nt:unstructured">
          <primaryType name="jcr:primaryType" propertyIndex="{Boolean}true" jcr:primaryType="nt:unstructured"/>
        </properties>
      </sling:Folder>
    </indexRules>
  </myIndex>
</jcr:root>

The query is restricted to return only sling:Folder nodes, so I do not think it will give you other node types as a result.

[NitroHazeDev]

Thanks @lukasz-m  much appreciated. I am avoiding index update at the  moment. But this is helpful . Thanks to you and @Anish-Sinha