0

In our scenario we have a (multivalued) category field on JCR:node and we want to query all nodes that do not have a current selection. In the JCR viewer the fields value is [] but I can't find any query to select nodes with this condition. We have tries:

SELECT * FROM [mgnl:page] as p WHERE p.[categories]=''

or

SELECT * FROM [mgnl:page] as p WHERE p.[categories]=[]

or

SELECT * FROM [mgnl:page] as p WHERE p.[categories] is null

But they aren't working or don't select the proper result. How can we write a query selecting these nodes?

Andre Albert
  • 1,386
  • 8
  • 17

1 Answers1

1

From the JSR 283:

5.10.3 Value Length
The length of a value in a single-value property, as defined in §3.6.7 Length of a Value, is returned by long Property.getLength()
Similarly, the method long[] Property.getLengths() is used to get an array of the lengths of all the values of a multi-value property.

From the JavaDocs:

    /**
     * Returns an array holding the lengths of the values of this (multi-value)
     * property in bytes where each is individually calculated as described in
     * {@link #getLength()}.
     * <p>
     * Returns a <code>-1</code> in the appropriate position if the
     * implementation cannot determine the length of a value.
     *
     * @return an array of lengths
     * @throws ValueFormatException if this property is single-valued.
     * @throws RepositoryException  if another error occurs.
     */
    public long[] getLengths() throws ValueFormatException, RepositoryException;

Unfortunately, this does not make it clear what the result of LENGTH([multivaluedProperty)] is in a SQL2 query.

Though, after some manual testing, it seems that the LENGTH operand returns some number smaller than 0. Therefore, you could try

select * from [nt:base] where LENGTH([multivaluedProperty]) < 0

Let me know whether this works for you :)

Tob
  • 352
  • 2
  • 8
  • 1
    Indeed, it is possible to query empty multivalued fields with LENGTH, but this is not combinable with an additional match condition. What is working is: `SELECT p FROM [mgnl:page] as p WHERE LENGTH(p.[mvProp])<0` but not: `SELECT p FROM [mgnl:page] as p WHERE LENGTH(p.[mvProp])<0 or p.[mvProp]='foobar'` . The second query causes `javax.jcr.UnsupportedRepositoryOperationException` as he seems to switch to a Lucene query due to the text-search (I guess) – Andre Albert Jan 10 '23 at 11:43