Expand my Community achievements bar.

Radically easy to access on brand approved content for distribution and omnichannel performant delivery. AEM Assets Content Hub and Dynamic Media with OpenAPI capabilities is now GA.
SOLVED

How to filter SQL2 query based on a property in Parent node (2nd level)

Avatar

Level 4

Hi,

I have a query which returns the dam:Asset nodes. However, I have to add a clause to filter out further based on the property in 2nd level of parent node (sling:Folder)

Present Query looks like this: SELECT PATH FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/my-org/testdev25ex])

The result set of above should be further filtered by checking a jcr property in 2nd level of parent folder which is always a sling:Folder
I can always work on results and filter out programmatically. However, i want to apply this filter in the query itself.

Anyone have idea how can we achieve this ?

Node structure would look like this:

Marked images in the green should basically filtered further based on the property in second level of parent node which is testdev25ex 

mrudul_0-1690303097219.png

 

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

Hi ,

 

To filter based on a property in the 2nd level of the parent node (sling:Folder), use the JOIN clause in your query:

 

SELECT asset.[jcr:path] FROM [dam:Asset] AS asset
JOIN [sling:Folder] AS folder ON ISCHILDNODE(asset, folder)
WHERE ISDESCENDANTNODE(asset, [/content/dam/my-org/testdev25ex])
AND folder.[propertyName] = 'desiredValue'
 

View solution in original post

2 Replies

Avatar

Community Advisor

I have not tested this, but would this work?

SELECT asset.[jcr:path]
FROM [dam:Asset] AS asset
JOIN [nt:base] AS parentFolder ON ISCHILDNODE(asset, parentFolder)
WHERE ISDESCENDANTNODE(asset, '/content/dam/my-org/testdev25ex')
AND parentFolder.[sling:resourceType] = 'sling:Folder'
AND parentFolder.[yourProperty] = 'Your Value'

Avatar

Correct answer by
Employee Advisor

Hi ,

 

To filter based on a property in the 2nd level of the parent node (sling:Folder), use the JOIN clause in your query:

 

SELECT asset.[jcr:path] FROM [dam:Asset] AS asset
JOIN [sling:Folder] AS folder ON ISCHILDNODE(asset, folder)
WHERE ISDESCENDANTNODE(asset, [/content/dam/my-org/testdev25ex])
AND folder.[propertyName] = 'desiredValue'