Search for Null Properties
Today I give you a useful tip. Well, actually Thibaud Arguillere gives you a useful tip since he answered this question on Nuxeo Answers. It’s really helpful so we thought it deserved its own blog post.
We had someone asking how to search for a document with an empty or null property. This is possible using NXQL as of Nuxeo Platform 5.4.2.
Let’s take a simple example with the dc:author property. It’s a simple String so the query is pretty simple. It would look like this:
SELECT * FROM Document WHERE dc:author IS NULL
See? This is pretty straight forward. Now, the example taken from Nuxeo Answers was about the dc:subjects property. As Thibaud wrote, you can use the following query for this:
SELECT * FROM Document WHERE dc:subjects/* IS NULL
It’s a little different because the subject’s property is a list of String, hence the addition of _/*_. Adding the usual filters on deleted documents or versions, the query looks like this:
SELECT * FROM Document WHERE dc:subjects/* IS NULL AND ecm:mixinType != 'HiddenInNavigation' AND ecm:isCheckedInVersion = 0 AND ecm:currentLifeCycleState != 'deleted'
Now don’t forget that you can also search for non null properties. You just have to add NOT:
SELECT * FROM Document WHERE dc:subjects/* IS NOT NULL
Looking for null or non null properties can be useful; for example, with escalation rules in a workflow or any listener triggered by a scheduler.