Using regex in SQL2 | Community
Skip to main content
S
Souradeep
New Participant
October 16, 2015
Solved

Using regex in SQL2

  • October 16, 2015
  • 11 replies
  • 6703 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by gopalKa

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

11 replies

G
gopalKa
Employee
October 16, 2015

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

S
SouradeepAuthor
New Participant
October 16, 2015

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

G
gopalKaAccepted solution
Employee
October 16, 2015

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

S
SouradeepAuthor
New Participant
October 16, 2015

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.

S
SouradeepAuthor
New Participant
October 16, 2015

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?

G
gopalKa
Employee
October 16, 2015

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'

G
gopalKa
Employee
October 16, 2015

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.

S
SouradeepAuthor
New Participant
October 16, 2015

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.

S
SouradeepAuthor
New Participant
October 16, 2015

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

S
SouradeepAuthor
New Participant
October 16, 2015

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

Show more replies
Terms & ConditionsAccessibility statement

Loading footer...