JCR SQL2 Query - find the parent nodes that do not contain children of a type | Community
Skip to main content
N
NitroHazeDev
Level 9
January 25, 2022
Solved

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

  • January 25, 2022
  • 2 replies
  • 4745 views

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)

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Anish-Sinha

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.

2 replies

Anish-Sinha
Adobe Employee
Anish-SinhaAdobe Employee
Adobe Employee
January 26, 2022

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

 

    N
    NitroHazeDevAuthor
    Level 9
    January 26, 2022

    @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
      Adobe Employee
      Anish-SinhaAdobe EmployeeAccepted solution
      Adobe Employee
      January 26, 2022

      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.

        lukasz-m
        Community Advisor
        lukasz-mCommunity Advisor
        Community Advisor
        January 26, 2022

        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/%/%/%/%'
          N
          NitroHazeDevAuthor
          Level 9
          January 26, 2022

          @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
            Community Advisor
            lukasz-mCommunity Advisor
            Community Advisor
            January 26, 2022

            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.

              Terms & ConditionsAccessibility statement

              Loading footer...