JCR query by path

Question

i am trying to query a subtree for a given node type by a this query:

SELECT * FROM [my:Type] AS my WHERE PATH(my) LIKE '/content/subtree/%'

somehow it does not give any results - using ISDESCENDANTNODE works - but query performance is terrible as soon as i have >5k elements total - no matter if inside or outside my subtree.

Answer 1

I don't think that PATH(my) is valid JCR SQL or JCR 2.0 SQL-2 grammar. You should use SQL-2:

select * from [my:Type] where isdescendantnode('/content/subtree')

or XPath

/jcr:root/content//element(*, my:Type)

This may be slow because it will only use the index on the node type (the path is not indexed). If you need it to be faster, you could (for example) store the path as a property and then add the corresponding condition; however this will prevent fast move operations.