Jcr sql 2 query get results of containing page along with searched asset(s) metadata

[HimaNagaUdayKiran]

In acs commons we have reference report which will output the path and containing page of the given asset path or all the assets in the given folder path.

Now we have a scenario to write a query to render the containing page as well  the asset details

Example: path to be searched - /content/dam/we-retail/us/abc

We need the following deatils in output as

/content/dam/we-retail/us/abc/1.png  and its asset metadata along with containing page

/content/we-retail/us/en/men

/content/dam/we-retail/us/abc/2.png  and its asset metadata along with containing page

/content/we-retail/us/en/women...

[Himanshu_Singhal]

Not sure if I've understood it correctly. Do you want a query to get result of let's say to get all the page nodes where asset is used?
If so, you can use such queries.

SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE(s, "/content/sample") AND CONTAINS(s.*, '"/content/dam/z/system/archetypes/placeholder/placeholder.jpg"')

If your question is about getting both the pages as well as asset metadata details as part of same query, IMO, I don't think it's possible using JCR2 query. 
Correct me if I've it wrong!

[Jörg_Hoh · Accepted solution]

If you want to search pages, use the correct node type:

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE(s, "/content/sample") AND CONTAINS(s.*, '"/content/dam/z/system/archetypes/placeholder/placeholder.jpg"')

It will use the smaller /oak:index/cqPageLucene index instead of the huge /oak:index/lucene index, and that will give you a better performance. But of course you just the page as a result, not the individual component.