JCR SQL2 Multivalue properties search

Question

I want to do a search in the content repository using one or more of the values as an input parameter for a multivalue property Something like: find all nodes with the primary type 'nt:unstructured' whose property 'multiprop' (multivalue property) contains both values "one" and "two".

How would the queryString passed to queryManager.createQuery should loook like?

Thank you.

Mudcat

Answer 1

You can treat the criteria on multi-valued properties just like other criteria. For example, the following query will find all nodes that have a value of 'white dog' on the 'someProp' property:

SELECT * FROM [nt:unstructured] WHERE someProp = 'white dog'

If the 'someProp' property has multiple values, then a node with at least one value that satisfies the criteria will be included in the results.

To find nodes that have multiple values of a multi-valued property, simply AND together multiple criteria. For example, the following query will return all nodes that have both of the specified values:

SELECT * FROM [nt:unstructured] WHERE someProp = 'white dog' 
                                  AND someProp = 'black dog'

Any of the operators will work, including 'LIKE':

SELECT * FROM [nt:unstructured] WHERE someProp LIKE '%white%'  
                                  AND someProp LIKE '%black%'

Other combinations are possible, of course.

Aintab

Thank you. Questions, please : which is those two solution is more performant? Or they should be identical performance wise? – Mudcat

Now, before getting the answer I tried something like someProp LIKE '%black%' and it seems rather slow...Is this because I was using LIKE instead of = ? – Mudcat

Final question please : thinking about performance, would you rather a do a "manual" search using getNode and checking the values of each propery against the input parameter or you would do JCR SQL2 SELECT? Many thanks. – Mudcat

The details regarding how comparison is done are here: day.com/specs/jcr/2.0/6_Query.html#6.7.16%20Comparison – Prefab

Using LIKE operator will always be slower than using an '=' operator, because it has to do a lot more work. IIRC, the leading '%' is slower than if you only have a trailing '%'. – Aintab

Thank you, Randall and michid for the useful info. – Mudcat