How to Search for Documents That Have an Empty List Property

Wed 04 June 2014 By Laurent Doguin

Search for Null Properties 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.

Category: Product & Development
Tagged: How to