NXQL

An <operator> can be:

• AND
• OR
• =
• <> (or != for Java compatibility and to make it convenient to write XML files)
• <
• <=
• >
• >=
• [NOT] (LIKE|ILIKE) (only between an <identifier> and a <string>)
• STARTSWITH (only between an <identifier> and a <string>)

Be careful with Oracle when comparing a value with an empty string, as in Oracle an empty string is NULL. For instance dc:description <> '' will never match any document, and dc:description IS NULL will also match for an empty description.

An <expression> can be:

• <expression> <op> <expression>
• ( <expression> )
• <literal>
• <identifier>

An <op> can be:

• +
• -
• *
• /

A <literal> can be:

• <string>: a string delimited by single quotes (') or for Java compatibility double quotes ("). To use the string delimiter itself inside the string, it must be escaped by a backslash (\' or \") (this is contrary to the standard SQL syntax which would double the delimiter). The backslash itself is also escaped by a backslash (\\). The special \n, \r and \t can also be used.
• <integer>: an integer with optional minus sign.
• <float>: a float.
• TIMESTAMP <timestamp>: a timestamp in ISO format yyyy-MM-dd hh:mm:ss(._sss) (the space separator can be replaced by a T).
• DATE <date>: a date, converted internally to a timestamp by adding 00:00:00 to it.

A <literal-list> is a non empty comma-separated list of <literal>.

An <identifier> is a property identifier. Before Nuxeo 5.5, this can be only a simple property or a simple list property. Since Nuxeo 5.5, this can also be a complex property element, maybe including wildcards for list indexes (see below).

List properties

A Nuxeo property representing a list of simple values (like dc:subjects) can be queried as if it represented a simple value, and Nuxeo will automatically expand the query to match any of the value in the list. The following example will find the documents where any subject is foo:

Note that the above does not mean to find the documents where the list of subjects is exactly the list [foo]; NXQL (and indeed SQL) does not have enough expressivity for that (and it would be quite slow).

The above example shows the = operator, and the same semantics apply for the operators IN, LIKE and ILIKE.

When using negative queries, though, the semantics get a bit more complex. The following example will find the documents where no subject is foo:

Note that the above does not mean to find the documents where there is at least one subject that is not foo.

The above example shows the <> operator, and the same semantics apply for the other negative operators NOT IN, NOT LIKE and NOT ILIKE.

Since Nuxeo 5.5, the complex property syntax (described in detail further down) can be used to match single list elements. The following two queries will do the same thing:

There is however an important difference in the mechanism with which these two requests are executed internally. The first syntax (which also worked before Nuxeo 5.5) internally uses a SQL EXISTS and a subquery. The second one uses a SQL JOIN (with a SQL DISTINCT if SELECT * is used). The end result is usually the same unless you want to use AbstractSession.queryAndFetch with no DISTINCT to get to the actual matching subjects, then only the second form is usable

In the case where negative queries are used, however, the different execution mechanisms imply that the two syntaxes mean different things:

The first syntax, as already explained, will find the documents where no subject is foo.

The second syntax will find the documents where the is at least one subject which is not foo.

Complex properties

Since Nuxeo 5.5 you can refer to complex properties in NXQL, after the SELECT, in the WHERE clause, and in the ORDER BY clause (cf NXP-4464).

A complex property is a property of a schema containing <xs:simpleType> lists, or <xs:complexType> subelements or sequences of them.

For complex subproperties, like the length field of the content field of the file schema, you can refer to:

• content/length for the value of the subproperty.

For simple lists, like dc:subjects, you can refer to:

• dc:subjects/3 for the 4th element of the list (indexes start at 0),
• dc:subjects/* for any element of the list,
• dc:subjects/*1 for any element of the list, correlated with other uses of the same number after *.

For complex lists, like the elements of the files schema, you can refer to:

• files/3/file/length for the length of the 4th file (again, indexes start at 0),
• files/*/file/length for any length
• files/*1/file/length for any length, correlated with other uses of the same number after *.

It's important to note that if you use a * then the resulting SQL JOIN generated may return several resulting rows, which means that if you use the AbstractSession.queryAndFetch API you may get several results for the same document.

The difference between * and *1 gets important when you refer to the same expression twice, for instance if you want the documents with an optional attached of given characteristics, you must correlate the queries.

This returns the documents with an attached text file of length 0:

This returns the documents with an attached text file and an attached file of length 0:

Special NXQL properties

The following properties are not legal as document property names, but are allowed in NXQL.

ecm:uuid: the document id (DocumentModel.getId()).

ecm:parentId: the document parent id.

ecm:path: the document path (DocumentModel.getPathAsString()), it cannot be used in the <select-clause>.

ecm:name: the document name (DocumentModel.getName()).

ecm:pos: the document position in its parent, this is NULL in non-ordered folders. This is mainly used for ordering.

ecm:primaryType: the document type (DocumentModel.getType()).

ecm:mixinType: a list of the document facets (DocumentModel.getFacets()) with some restrictions. 1. the facet Immutable is never seen. 2. the facets Folderish and HiddenInNavigation are never seen on document instances (only if they're on the type). 3. like for other list properties, it can be used only with operators =, <>, IN and NOT IN.

ecm:isProxy: 1 for proxies and 0 for non-proxies (DocumentModel.isProxy()). This can only be compared to 1 or 0.

ecm:isCheckedInVersion: 1 for versions and 0 for non-version (DocumentModel.isVersion()). This can only be compared to 1 or 0.

ecm:currentLifeCycleState: the document lifecycle state (DocumentModel.getCurrentLifeCycleState()).

ecm:versionLabel: the version label for versions (DocumentModel.getVersionLabel() only for a version), NULL if it's not a version.

ecm:lockOwner: the lock owner (DocumentModel.getLockInfo().getOwner()). (Since Nuxeo 5.4.2)

ecm:lockCreated: the lock creation date (DocumentModel.getLockInfo().getCreated()). (Since Nuxeo 5.4.2)

ecm:lock: the old lock. (Deprecated since Nuxeo 5.4.2 and NXP-6054, now returns ecm:lockOwner, used to return a concatenation of the lock owner and a short-format creation date)

ecm:fulltext: a special field to make fulltext queries, see Fulltext queries for more.

ecm:tag: a tag of the document. This property, when used multiple times in the same query, always refers to the same tag. If you want to refer to multiple tags in the same query, you can use a wildcard syntax similar to complex properties: every instance of ecm:tag/* will always refer to a different tag. If you want to refer several times to the same tag but still have flexibility, use correlated wildcards like for complex properties: ecm:tag/*1, ecm:tag/*2, etc. See the examples below for more. (Since Nuxeo 5.7)

ecm:proxyTargetId: the id of the target of a proxy (usually a version). Implies a search for proxies (ecm:proxy = 1). (Since Nuxeo 5.7)

ecm:proxyVersionableId: the id of the versionable document of a proxy (the versionable document is the one from which the version to which the proxy is pointing was created). Implies a search for proxies (ecm:proxy = 1). (Since Nuxeo 5.7)

Examples

Since Nuxeo 5.4.1 you can use IS NULL:

Since Nuxeo 5.5 you can use complex properties:

Since Nuxeo 5.7 you can use ecm:tag:

Since Nuxeo 5.7 you can also use ecm:proxyTargetId and ecm:proxyVersionableId:

Fulltext examples

This uses standard SQL LIKE:

The following uses a fulltext index that has to be additionally configured by administrators:

The following uses a fulltext index if one is configured for the dc:title field, otherwise it uses ILIKE-based queries: