Using regex in SQL2

[Souradeep]

Hi,

Is there a way I can use regex or something similar to narrow down the number of nodes across which a query will search to find the results.

I am currently using like this:

SELECT * FROM [dam:Asset] AS s WHERE ISDESCENDANTNODE(s,'/content/dam/a') and [jcr:content/metadata/sku]='XYZ'

However I know my actual nodes are 3 levels deeper than /content/dam/a like /content/dam/a/b/c/product, /content/dam/a/d/e/product etc.

So I would like to do something like this:

SELECT * FROM [dam:Asset] AS s WHERE ISDESCENDANTNODE(s,'/content/dam/a/*/product') and [jcr:content/metadata/sku]='XYZ'

Let me know if this is possible without using multiple paths in the where condition. This will also improve the performance of my query as it will restrict the query to fewer folders.

Thanks,Souradeep

[kalyanar]

Why dont you index tht property rather than regex.It will be much faster.

[Souradeep]

Thanks for the update. Could you please provide the steps to do that?

[kalyanar · Accepted solution]

Please read through http://docs.adobe.com/docs/en/aem/6-0/deploy/upgrade/queries-and-indexing.html#Configuring%20the%20i... .Find a unique property through which you can identify your nodes and index it.

[Souradeep]

I went through the steps mentioned to create a index, but I don't think it is the solution to my problem.

What I am asking is we are looking to find the assets under a given path which has a given property. However there are multiple such paths here with similar pattern, so I would like to put something like regex to generalize the path. 

For example, the paths are like /content/dam/a/b/product, /content/c/d/product etc, I would like to have something like /content/*/product.

[Souradeep]

Thanks for the update, I also have another property for each asset which can tell me its in which path. If I use like predicate, it is working fine. Can you tell me if using an index property will better the performance than using this predicate?

[kalyanar]

If you have the index for the property that uniquely identifies the product node, it should be enough . SELECT * FROM [dam:Asset] AS s WHERE ISDESCENDANTNODE(s,'/content/dam/a/*/product') and [jcr:content/metadata/sku]='XYZ'

will become

SELECT * FROM [dam:Asset] AS s WHERE productProperty="product" and [jcr:content/metadata/sku]='XYZ'

[kalyanar]

At this link you will find explain query tool (like explain plan in sql). http://adobe-consulting-services.github.io/acs-aem-tools/explain-query.html

This will tell you which indexes are used and you can try using different indexes and test.

[Souradeep]

I understand when you said to index the property, the thing is in that case I would have to add an additional distinguishable property for each asset which will tell me that its the asset I am looking for. Is there a way I can do it without creating a new property just by utilizing the path.

[Souradeep]

Hi Kalyanar,

I checked the explain plan after adding a Property Index and its giving much better performance, many thanks for sharing this.

Thanks, Souradeep

[Souradeep]

Hi Kalyanar,

Could you please answer one more thing, how to set the indexing happen automatically. Setting the reindex property to true triggers the indexing and then it is set to false once the indexing has happened. So when a new record is added which has the indexed property, do I need to run the re-indexing again to include this record in my result set or it happens automatically.

Thanks, Souradeep

[kalyanar]

By default indexing is transactional unless you add async property. So it will happen as soon as you update a node.