JCR SQL2 query for an empty multivalued field

Question

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?

Answer 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 :)