JCR SQL2: Exact keyword search for multivalued properties

[DJSarkar]

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.

[DEBAL_DAS · Accepted solution]

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 -

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'

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

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 -

[DJSarkar]

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!

[DJSarkar]

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.

[DEBAL_DAS]

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'

[arunpatidar]

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'

[DJSarkar]

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