Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

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

Avatar

Level 10

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)

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

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.

View solution in original post

9 Replies

Avatar

Employee Advisor

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

 

Avatar

Level 10

@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?

Avatar

Correct answer by
Employee Advisor

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.

Avatar

Level 10

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

Avatar

Level 10

@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

Avatar

Community Advisor

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/%/%/%/%'

Avatar

Level 10

@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?

 

Avatar

Community Advisor

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.

Avatar

Level 10

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