Expand my Community achievements bar.

SOLVED

Using regex in SQL2

Avatar

Level 3

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

1 Accepted Solution

Avatar

Correct answer by
Employee

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.

View solution in original post

11 Replies

Avatar

Employee

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

Avatar

Level 3

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

Avatar

Correct answer by
Employee

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.

Avatar

Level 3

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.

Avatar

Level 3

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?

Avatar

Employee

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'

Avatar

Employee

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.

Avatar

Level 3

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.

Avatar

Level 3

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

Avatar

Level 3

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

Avatar

Employee

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