The general syntax of a NXQL expression is:
The <select-clause> is a comma-separated list of properties. Properties are Nuxeo document property names, for instance
The <from-clause> is a comma-separated list of document types.
The optional <where-clause> is a general <predicate>.
The optional <order-by-clause> is a comma-separated list of <identifier>, each one being optionally followed by
A <predicate> can be:
An <operator> can be:
!=for Java compatibility and to make it convenient to write XML files)
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
An <expression> can be:
- <expression> <op> <expression>
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 (
\") (this is contrary to the standard SQL syntax which would double the delimiter). The backslash itself is also escaped by a backslash (
\\). The special
\tcan also be used.
- <integer>: an integer with optional minus sign.
- <float>: a float.
TIMESTAMP<timestamp>: a timestamp in ISO format yyyy
._sss) (the space separator can be replaced by a
DATE<date>: a date, converted internally to a timestamp by adding
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).
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
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 LIKE and
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
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.
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/lengthfor the value of the subproperty.
For simple lists, like
dc:subjects, you can refer to:
dc:subjects/3for the 4th element of the list (indexes start at 0),
dc:subjects/*for any element of the list,
dc:subjects/*1for 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/lengthfor the length of the 4th file (again, indexes start at 0),
files/*/for any length
files/*1/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
*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 (
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 (
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 (
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
1 for proxies and
0 for non-proxies (
DocumentModel.isProxy()). This can only be compared to
1 for versions and
0 for non-version (
DocumentModel.isVersion()). This can only be compared to
ecm:currentLifeCycleState: the document lifecycle state (
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)
Since Nuxeo 5.4.1 you can use
Since Nuxeo 5.5 you can use complex properties:
Since Nuxeo 5.7 you can use
Since Nuxeo 5.7 you can also use
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: