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.

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.