Expand my Community achievements bar.

Don’t miss the AEM Skill Exchange in SF on Nov 14—hear from industry leaders, learn best practices, and enhance your AEM strategy with practical tips.
SOLVED

JCR SQL2: Exact keyword search for multivalued properties

Avatar

Level 2

I’m trying to build a SQL2 Query where I want to search a String value within a multivalued property.

e.g. something similar to this —>   SELECT * FROM [nt:unstructured] AS node WHERE ISDESCENDANTNODE(node,’/content/a/b/c’) AND CONTAINS(node.[MULTI_PROP], ‘hello’);

Here MULTI_PROP is a String[ ] property of the target node and it should have exact same string value i.e. “hello”

But there are other nodes in same descendent level where the property values are “hello world” / “hello again” etc. So, these nodes are also getting considered as CONTAINS has been used.

 

So, while I tried couple of other ways, it did nt work out with any of those : 

i. Tried using LIKE operator —>  0 result

ii. Tried using with double quotations —> 0 result

ii. Tried IN operator —> 0 result

 

So, any help would be great on this topic. As I don’t want further filtration/iteration logic to be applied in service and want to retrieve the filtered result only through query.

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

In below screenshot you could see , cq:tags is a multi value property String[] and I would like to get list of pages with where I have added 'we-retail:activity/biking' tag using JCR SQL2 query -

 

DEBAL_DAS_0-1650439884188.png

 

Here is my JCR SQL2 query with results-

 

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE([/content/we-retail/language-masters/en]) and [jcr:content/cq:tags] = 'we-retail:activity/biking'

 

DEBAL_DAS_1-1650440108223.png

 

Could you please give a try with similar approach and let us know.

 

Below you could see I have 4 different messages with multi value property named: messages

DEBAL_DAS_0-1650440548052.pngDEBAL_DAS_1-1650440560996.pngDEBAL_DAS_2-1650440569359.png

DEBAL_DAS_3-1650440578220.png

 

 

 

I want to identify list of pages with hello against messages (String[]) and here is my query -

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE([/content/we-retail/language-masters/en]) and [jcr:content/messages] = 'hello'

 

Query results -

 

DEBAL_DAS_4-1650440755811.png

 

View solution in original post

6 Replies

Avatar

Correct answer by
Employee Advisor

In below screenshot you could see , cq:tags is a multi value property String[] and I would like to get list of pages with where I have added 'we-retail:activity/biking' tag using JCR SQL2 query -

 

DEBAL_DAS_0-1650439884188.png

 

Here is my JCR SQL2 query with results-

 

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE([/content/we-retail/language-masters/en]) and [jcr:content/cq:tags] = 'we-retail:activity/biking'

 

DEBAL_DAS_1-1650440108223.png

 

Could you please give a try with similar approach and let us know.

 

Below you could see I have 4 different messages with multi value property named: messages

DEBAL_DAS_0-1650440548052.pngDEBAL_DAS_1-1650440560996.pngDEBAL_DAS_2-1650440569359.png

DEBAL_DAS_3-1650440578220.png

 

 

 

I want to identify list of pages with hello against messages (String[]) and here is my query -

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE([/content/we-retail/language-masters/en]) and [jcr:content/messages] = 'hello'

 

Query results -

 

DEBAL_DAS_4-1650440755811.png

 

Avatar

Level 2

UPDATED: Thank you Debal for sharing the information. This looked fine for me; I was having a typo earlier with duplicated string value and hence, was facing the same. thanks!

Avatar

Level 2

Also, noticed a bit odd scenario where if the property name is "tags" , then the same query is not working whereas same query with any other custom property e.g. messages/abc etc.. is working fine.

Is there any default constraint that any custom multivalued property named as "tags" should not be considered for querying. for e.g. 

SELECT * FROM [cq:PageContent]  ............... AND node.[abc] = 'hello' - working fine

SELECT * FROM [cq:PageContent]  ............... AND node.[tags] = 'hello' - not working

Where as "tags" and "abc" both are multivalued properties existing at same node.

Avatar

Employee Advisor

With property name "tags" , it is working for me as shown below -

Query : SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE([/content/we-retail/language-masters/en]) and s.[tags] = 'Debal'

DEBAL_DAS_0-1650458452204.png

 

DEBAL_DAS_1-1650458471993.png

DEBAL_DAS_2-1650458485770.pngDEBAL_DAS_3-1650458494533.pngDEBAL_DAS_4-1650458502979.png

 

Avatar

Community Advisor

Hi,

As @DEBAL_DAS said, it will work with = operator

SELECT * FROM [nt:unstructured] AS node WHERE ISDESCENDANTNODE(node,’/content/a/b/c’) AND node.[MULTI_PROP] = 'hello'

 



Arun Patidar

Avatar

Level 2

Updated: Thank you Arun. Yes, it worked fine for the single value in the multi valued attributes.