JCR SQL2: Exact keyword search for multivalued properties | Community
Skip to main content
D
DJSarkar
Level 2
April 20, 2022
Solved

JCR SQL2: Exact keyword search for multivalued properties

  • April 20, 2022
  • 2 replies
  • 2514 views

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.

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 DEBAL_DAS

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 -

 

 

2 replies

DEBAL_DAS
DEBAL_DASAccepted solution
April 20, 2022

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 -

 

 

D
DJSarkarAuthor
Level 2
April 20, 2022

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
DEBAL_DAS
April 20, 2022

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
    Community Advisor
    arunpatidarCommunity Advisor
    Community Advisor
    April 20, 2022

    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
    D
    DJSarkarAuthor
    Level 2
    April 20, 2022

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

    Terms & ConditionsAccessibility statement

    Loading footer...