NXQL syntax
The general syntax of a NXQL expression is:
SELECT (*|[DISTINCT] <select-clause>) FROM <from-clause> [WHERE <where-clause>]
The <select-clause> is a comma-separated list of properties. Properties are Nuxeo document property names, for instance dc:modified, or special properties, for instance ecm:uuid (see below).
The <from-clause> is a comma-separated list of document types.
The optional <where-clause> is a general <predicate>.
A <predicate> can be:
- <predicate> <operator> <predicate>
- <identifier> [NOT] IN ( <literal-list> )
- <identifier> [NOT] BETWEEN <literal> AND <literal>
- <identifier> IS [NOT] NULL (since Nuxeo 5.4.2, cf NXP-4339)
- ( <predicate> )
- NOT <predicate>
- <expression>
An <operator> can be:
- AND
- OR
- =
- <> (or != for Java compatibility)
- <
- <=
- >
- >=
- [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 ("). Inside a string, special characters are escaped by a backslash (\), including for the quotes or double quotes contrary to the standard SQL syntax which would double them. The special \n, \r, \t and \\ 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).
Complex property references
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 3rd 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/length for the length of the 3rd file,
- files/*/length for any length
- files/*1/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:
For simple lists, there is a slight difference in doing these two requests:
The first one internally uses a SQL EXISTS and a subquery, and also worked before Nuxeo 5.5. 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.
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.
Examples
Since Nuxeo 5.4.1 you can use IS NULL:
Since Nuxeo 5.5 you can use complex properties:
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: