Home > Developer Documentation Center > Nuxeo Server > Indexing and Query > NXQL
Nuxeo Server

NXQL

Updated: March 18, 2024

NXQL Syntax

The general syntax of a NXQL expression is:

SELECT (*|[DISTINCT] <select-clause>) FROM <from-clause> [WHERE <where-clause>] [ ORDER BY <order-by-clause> ]

The <select-clause> is a comma-separated list of <property>, or <aggregate> since Nuxeo 5.9.2.

A <property> is a Nuxeo document property name, for instance dc:modified , or a special properties, for instance ecm:uuid (see below).

An <aggregate> is of the form <agg-function> ( _<property>_ ) (available since Nuxeo 5.9.2, see NXP-13708).

An <agg-function> can be:

  • COUNT
  • AVG
  • SUM
  • MIN
  • MAX

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 ASC or DESC (ASC is the default).

A <predicate> can be:

  • <predicate> <operator> <predicate>
  • <identifier> [NOT] IN ( <literal-list> )
  • <identifier> [NOT] BETWEEN <literal> AND <literal>
  • <identifier> IS [NOT] NULL
  • ( <predicate> )
  • NOT <predicate>
  • <expression>

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 : a date in ISO format yyyy - _MM_ - _dd_, 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. This can be only a simple property, a simple list property or a complex property element, maybe including wildcards for list indexes (see below).

Operators

The semantics of a few specific operators is described below.

LIKE (and ILIKE) behave like in the SQL standard, meaning that the _ and % characters are wildcards: _ matches a single arbitrary character, and % matches any number of characters (including zero).

STARTSWITH matches path-like values. When used with ecm:path, it matches documents whose path is strictly under the specified path. When used with an arbitrary string-based property, it assumes that the property value has the form of a /-separated path, and then matches if the value is equal to or under the specified path.

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:

SELECT * FROM Document WHERE dc:subjects = '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:

SELECT * FROM Document WHERE dc:subjects <> '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.

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:

SELECT * FROM Document WHERE dc:subjects = 'foo'
SELECT * FROM Document WHERE dc:subjects/* = 'foo'

There is however an important difference in the mechanism with which these two requests are executed internally. The first syntax 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 CoreSession.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:

SELECT * FROM Document WHERE dc:subjects <> 'foo'
SELECT * FROM Document WHERE dc:subjects/* <> 'foo'     -- not the same thing as above

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

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

To find documents whose list field is empty, you will use the /* syntax:

SELECT * FROM Document WHERE dc:subjects/* IS NULL

Complex Properties

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 CoreSession.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:

SELECT * FROM Document WHERE files/*1/file/name LIKE '%.txt' AND files/*1/file/length = 0

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

SELECT * FROM Document WHERE files/*/file/name LIKE '%.txt' AND files/*/file/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()). This is a synthetic value (computed from the ecm:name of the document's ancestors) and therefore cannot be used in all contexts. It cannot be used in the <select-clause>. In the <where-clause> it may only be used with operators =, <> (or !=) and STARTSWITH. Using it in the <order-by-clause> carries a large performance penalty when using CoreSession.query, and it cannot be used in the <order-by-clause> when using CoreSession.queryAndFetch. See also ecm:ancestorId.

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 in the <order-by-clause>.

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:currentLifeCycleState: the document lifecycle state (DocumentModel.getCurrentLifeCycleState()).

ecm:isCheckedIn: 1 if the document is checked in and 0 if not (the opposite of DocumentModel.isCheckedOut()). This can only be compared to 1 or 0. (Since Nuxeo 5.7.3)

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

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

ecm:isCheckedInVersion: deprecated name for ecm:isVersion.

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

ecm:versionDescription: the version description for versions, NULL if it's not a version. (Since Nuxeo 5.7.3)

ecm:versionCreated: the version creation time for versions, NULL if it's not a version. (Since Nuxeo 5.7.3)

ecm:versionVersionableId: the id of the versionable document of a version (the versionable document is the one from which the version was created). (Since Nuxeo 5.7.3)

ecm:isLatestVersion: 1 if this is the latest version of a document, 0 if not. This can only be compared to 1 or 0. (Since Nuxeo 5.7.3)

ecm:isLatestMajorVersion: 1 if this is the latest major version of a document, 0 if not. This can only be compared to 1 or 0. (Since Nuxeo 5.7.3)

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

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:isProxy = 1). (Since Nuxeo 5.7.1)

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

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

ecm:lock: deprecated name for ecm:lockOwner.

ecm:fulltext: a special field to make full-text queries, see Full-Text Queries for more.

ecm:fulltextScore: a special field representing the full-text score. This can be used in an ORDER BY clause, or in the SELECT clause when using CoreSession.queryAndFetch. It must be used in conjunction with an ecm:fulltext match in the WHERE clause. (Since Nuxeo 5.9.6)

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 lists: ecm:tag/*1, ecm:tag/*2, etc. See the examples below for more. (Since Nuxeo 5.7.1)

ecm:ancestorId: the id of an ancestor of the document (parent, grand-parent, etc., up to the root). This is only useable in a WHERE clause with the syntax ecm:ancestorId = 'some-doc-id' or ecm:ancestorId <> 'some-doc-id'. It's an alternative to ecm:path STARTSWITH '/some/doc/path' if you know the id instead of the path. See also ecm:path. (Since Nuxeo 5.9.6)

ecm:acl: a pseudo-list giving access to ACLs, which needs to be used with a suffix specifying which part of the ACL is accessed. Available are ecm:acl/*/principal (ACL principal), ecm:acl/*/permission (ACL permission), ecm:acl/*/grant (ACL grant/deny, a boolean), ecm:acl/*/name (ACL name), ecm:acl/*/pos (ACL position). If you want to refer several times to the same ACL but still have flexibility, use correlated wildcards like for lists: ecm:acl/*1/principal, ecm:acl/*2/permission, etc. See the examples below for more. Note that this is a query on the ACLs that were set on a specific folder or document, NOT on the resolved permissions according to the inheritance rules. (Since Nuxeo 6.0-HF06 or Nuxeo 7.2)

Examples

SELECT * FROM Document
SELECT * FROM Folder
SELECT * FROM File
SELECT * FROM Note
SELECT * FROM Note, File WHERE dc:title = 'My Doc'
SELECT * FROM Document WHERE NOT dc:title = 'My Doc'
SELECT * FROM Document WHERE dc:title = 'My Doc' OR dc:title = 'My Other Doc'
SELECT * FROM Document WHERE (dc:title = 'blah' OR ecm:isProxy = 1) AND dc:contributors = 'bob'
SELECT * FROM Document WHERE file:content/name = 'testfile.txt'
SELECT * FROM Document WHERE uid = 'isbn1234'
SELECT * FROM Document WHERE file:content/name = 'testfile.txt' OR dc:title = 'testfile3_Title'
SELECT * FROM Document WHERE file:content/name = 'testfile.txt' OR dc:contributors = 'bob'
SELECT * FROM Document WHERE dc:created BETWEEN DATE '2007-03-15' AND DATE '2008-01-01'
SELECT * FROM Document WHERE dc:created NOT BETWEEN DATE '2007-01-01' AND DATE '2008-01-01' -- (VCS only)
SELECT * FROM Document WHERE dc:contributors = 'bob'
SELECT * FROM Document WHERE dc:contributors IN ('bob', 'john')
SELECT * FROM Document WHERE dc:contributors NOT IN ('bob', 'john')
SELECT * FROM Document WHERE dc:contributors <> 'pete'
SELECT * FROM Document WHERE dc:contributors <> 'blah'
SELECT * FROM Document WHERE dc:contributors <> 'blah' AND ecm:isProxy = 0
SELECT * FROM Document WHERE dc:title LIKE 'testfile%' ORDER BY dc:description
SELECT * FROM Document WHERE dc:title LIKE 'testfile%' ORDER BY dc:description DESC
SELECT * FROM Document ORDER BY ecm:path
SELECT * FROM Document ORDER BY ecm:path DESC
SELECT * FROM Document ORDER BY ecm:name
SELECT * FROM Document WHERE ecm:path STARTSWITH '/'
SELECT * FROM Document WHERE ecm:path STARTSWITH '/nothere'
SELECT * FROM Document WHERE ecm:path STARTSWITH '/testfolder1'
SELECT * FROM Document WHERE dc:title = 'testfile1_Title' AND ecm:path STARTSWITH '/foo'
SELECT * FROM Document WHERE dc:title LIKE 'testfile%' AND ecm:path STARTSWITH '/bar'
SELECT * FROM Document WHERE dc:coverage STARTSWITH 'foo'
SELECT * FROM Document WHERE dc:coverage STARTSWITH 'foo/bar'
SELECT * FROM Document WHERE dc:subjects STARTSWITH 'gee'
SELECT * FROM Document WHERE dc:subjects STARTSWITH 'gee/moo'
SELECT * FROM Document WHERE dc:created >= DATE '2007-01-01'
SELECT * FROM Document WHERE dc:created >= TIMESTAMP '2007-03-15 00:00:00'
SELECT * FROM Document WHERE dc:created >= DATE '2007-02-15' AND dc:created <= DATE '2007-03-15'
SELECT * FROM Document WHERE my:boolean = 1
SELECT * FROM Document WHERE ecm:isProxy = 1
SELECT * FROM Document WHERE ecm:isVersion = 1
SELECT * FROM Document WHERE ecm:isProxy = 0 AND ecm:isVersion = 0
SELECT * FROM Document WHERE ecm:uuid = 'c5904f77-299a-411e-8477-81d3102a81f9'
SELECT * FROM Document WHERE ecm:name = 'foo'
SELECT * FROM Document WHERE ecm:parentId = '5442fff5-06f1-47c9-ac59-1e10ef8e985b'
SELECT * FROM Document WHERE ecm:primaryType = 'Folder'
SELECT * FROM Document WHERE ecm:primaryType <> 'Folder'
SELECT * FROM Document WHERE ecm:primaryType = 'Note'
SELECT * FROM Document WHERE ecm:primaryType IN ('Folder', 'Note')
SELECT * FROM Document WHERE ecm:primaryType NOT IN ('Folder', 'Note')
SELECT * FROM Document WHERE ecm:mixinType = 'Versionable' AND ecm:mixinType <> 'Downloadable'
SELECT * FROM Document WHERE ecm:mixinType <> 'Rendition'
SELECT * FROM Document WHERE ecm:mixinType = 'Rendition' AND dc:title NOT ILIKE '%pdf'
SELECT * FROM Document WHERE ecm:mixinType = 'Folderish'
SELECT * FROM Document WHERE ecm:mixinType = 'Downloadable'
SELECT * FROM Document WHERE ecm:mixinType = 'Versionable'
SELECT * FROM Document WHERE ecm:mixinType IN ('Folderish', 'Downloadable')
SELECT * FROM Document WHERE ecm:mixinType NOT IN ('Folderish', 'Downloadable')
SELECT * FROM Document WHERE ecm:currentLifeCycleState = 'project'
SELECT * FROM Document WHERE ecm:versionLabel = '1.0'
SELECT * FROM Document WHERE ecm:currentLifeCycleState <> 'deleted'
SELECT * FROM Document WHERE ecm:fulltext = 'world'
SELECT * FROM Document WHERE dc:title = 'hello world 1' ORDER BY ecm:currentLifeCycleState
SELECT * FROM Document WHERE dc:title = 'hello world 1' ORDER BY ecm:versionLabel
SELECT * FROM Document WHERE ecm:parentId = '62cc5f29-f33e-479e-b122-e3922396e601' ORDER BY ecm:pos

You can use IS NULL:

SELECT * FROM Document WHERE dc:expired IS NOT NULL
SELECT * FROM Document WHERE dc:language = '' OR dc:language IS NULL

You can use complex properties:

SELECT * FROM File WHERE content/length > 0
SELECT * FROM File WHERE content/name = 'testfile.txt'
SELECT * FROM File ORDER BY content/length DESC
SELECT * FROM Document WHERE tst:couple/first/firstname = 'Steve'
SELECT * FROM Document WHERE tst:friends/0/firstname = 'John'
SELECT * FROM Document WHERE tst:friends/*/firstname = 'John'
SELECT * FROM Document WHERE tst:friends/*1/firstname = 'John' AND tst:friends/*1/lastname = 'Smith'
SELECT tst:friends/*1/lastname FROM Document WHERE tst:friends/*1/firstname = 'John'
SELECT * FROM Document WHERE dc:subjects/0 = 'something'
SELECT * FROM Document WHERE dc:subjects/* = 'something'
SELECT dc:subjects/*1 FROM Document WHERE dc:subjects/*1 LIKE 'abc%'

Since Nuxeo 5.7.1 you can use ecm:tag:

SELECT * FROM Document WHERE ecm:tag = 'tag1'
SELECT * FROM Document WHERE ecm:tag IN ('tag1', 'tag2')               -- documents with either tag
SELECT * FROM Document WHERE ecm:tag/* = 'tag1' AND ecm:tag/* = 'tag2' -- documents with both tags
SELECT ecm:tag FROM Document WHERE dc:title = 'something'              -- with queryAndFetch
SELECT ecm:tag FROM Document WHERE ecm:tag LIKE 'abc%'                 -- with queryAndFetch
SELECT ecm:tag/*1 FROM Document WHERE ecm:tag/*1 LIKE 'abc%' AND ecm:tag/*2 = 'tag1'
SELECT ecm:tag FROM Document WHERE ecm:tag LIKE 'abc%' AND ecm:tag/* = 'tag1'  -- simpler version of above

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

SELECT * FROM Document WHERE ecm:proxyTargetId = '62cc5f29-f33e-479e-b122-e3922396e601'
SELECT * FROM Document WHERE ecm:proxyVersionableId = '5442fff5-06f1-47c9-ac59-1e10ef8e985b'

Since Nuxeo 5.7.3 you can match the "checked in" state of a document:

 SELECT * FROM Document WHERE ecm:isCheckedIn = 1

Since Nuxeo 5.7.3 you can use additional version-related properties:

SELECT * FROM Document WHERE ecm:isVersion = 1                        
SELECT * FROM Document WHERE ecm:isLatestVersion = 1
SELECT * FROM Document WHERE ecm:isLatestMajorVersion = 1
SELECT * FROM Document WHERE ecm:versionCreated >= TIMESTAMP '2007-03-15 00:00:00'
SELECT * FROM Document WHERE ecm:versionLabel = '1.0'                 -- this was available even before Nuxeo 5.7.3
SELECT * FROM Document WHERE ecm:versionDescription LIKE '%TODO%'
SELECT * FROM Document WHERE ecm:versionVersionableId = '5442fff5-06f1-47c9-ac59-1e10ef8e985b'

Since Nuxeo 5.9.2 you can use aggregates in queries made with CoreSession.queryAndFetch:

SELECT COUNT(ecm:uuid) FROM Document WHERE dc:title = 'foo'
SELECT MIN(my:value), MAX(my:value) FROM Document WHERE dc:title = 'bar'

Since Nuxeo 5.9.6 you can use ecm:ancestorId = with an id as an alternative to ecm:path STARTSWITH with a path:

SELECT * FROM Document WHERE ecm:ancestorId = 'c5904f77-299a-411e-8477-81d3102a81f9'
SELECT * FROM Document WHERE ecm:ancestorId <> 'c5904f77-299a-411e-8477-81d3102a81f9'

Since Nuxeo 6.0-HF06 or Nuxeo 7.2 you can use ecm:acl (usually with correlated wildcards):

SELECT * FROM Document WHERE ecm:acl/*1/principal = 'bob'
                         AND ecm:acl/*1/grant = 1
                         AND ecm:acl/*1/permission IN ('Browse', 'Read', 'ReadProperties', 'ReadWrite', 'ReadRemove', 'Everything')

Fulltext Examples

This uses standard SQL LIKE and usually has poor performance:

SELECT * FROM Document WHERE dc:title LIKE 'Test%'
SELECT * FROM Document WHERE dc:title ILIKE 'test%'
SELECT * FROM Document WHERE dc:contributors LIKE 'pe%'
SELECT * FROM Document WHERE dc:subjects LIKE '%oo%'
SELECT * FROM Document WHERE dc:subjects NOT LIKE '%oo%'

This is a standard full-text query (see Full-Text Queries for more):

SELECT * FROM Document WHERE ecm:fulltext = 'foo'

The following uses a full-text index that has to be additionally configured by administrators:

SELECT * FROM Document WHERE ecm:fulltext_title = 'world'

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

SELECT * FROM Document WHERE ecm:fulltext.dc:title = 'brave'

Since Nuxeo 5.9.6, use can express an explicit order on the full-text score, and request it from CoreSession.queryAndFetch:

SELECT * FROM Document WHERE ecm:fulltext = 'foo' ORDER BY ecm:fulltextScore DESC
SELECT ecm:uuid, ecm:fulltextScore FROM Document WHERE ecm:fulltext = 'foo'        -- with queryAndFetch

Notes about MongoDB

The above describes the NXQL capabilities for a VCS repository, but if MongoDB is used a few limitations to the query model have to be taken into account.

MongoDB NXQL Limitations

The following limitations apply:

  • Predicates of the form <predicate> <operator> <predicate> are restricted to the form <identifier> <operator> <literal>. This means that for instance the following are not allowed in a NXQL query on a MongoDB repository:
    • ... WHERE dc:modified = dc:issued (not allowed, cannot use two properties in a predicate)
    • ... WHERE 123 = tst:value (not allowed, property must be on the left-hand side)
    • ... WHERE tst:value = 1000*1000 (not allowed, right-hand side must be a literal, not an expression)
  • Aggregates (COUNT, AVERAGE, MAX, MIN) are not supported.
  • DISTINCT is supported only for ecm:uuid.

Notes about Elasticsearch

When an NXQL query is processed by an Elasticsearch PageProvider or using the Nuxeo Elasticsearch search service, there are some limitations, specific configuration and more features available.

Elasticsearch NXQL Limitations

  • Complex property correlations are not supported.

    Queries like this will not return any document:

    files:files/*1/file/name LIKE '%.txt' AND files:files/*1/file/length = 0 AND dc:subjects/3 = 'foo'

    Note that the match any /* is supported.

  • Canonical name for complex properties without prefix are not supported. You need to write file:content/name and not just content/name. See NXP-15426 for more information.

  • When using query, queryAndFetch or queryAndAggregate the select clause is limited to scalar properties: There is no support of NXQL aggregate like: DISTINCT, COUNT, AVERAGE, MAX, MIN, or operators like: + - / *.
  • Custom securityPolicy expressible in NXQL is taken in account since NXP-17371, if the securityPolicy is not expressible in NXQL then total count or aggregates counts can be wrong.
  • ecm:acl and ecm:tag queries are not supported.

Required Elasticsearch Configuration

Mapping

Without a proper mapping the following NXQL operators will not work by default:

  • Full-text search: ecm:fulltext.my:field = 'foo' works only if there is a multi field my:field.fulltext defined in the mapping.
  • ILIKE: my:field ILIKE 'Foo' works only if there is a multi field my:field.lowercase defined in the mapping.
  • STARTSWITH: my:field STARTSWITH '/foo/bar': works only if there is a multi field my:field.children defined with proper analyzer. Note that ecm:path.children is already defined in the default mapping..

Visit the mapping documentation for more information.

Full-Text Search

The full-text search is not configured the same way:

  • The ecm:fulltext matches the all_field field which is the concatenation of all fields. This is different from the NXQL ecm:fulltext which matches only some explicit fields. You can adapt the mapping to exclude some fields.
  • Custom full-text indexes are not supported. ecm:fulltext_someindex will match the all_field field. It is possible to select a list of field using hints, see below.
  • In addition to the NXQL full-text syntax, it is also possible to use the Elasticsearch simple query string syntax.

Elasticsearch NXQL Hints

NXQL hints enable to use more Elasticsearch operators.

The Elasticsearch hint syntax takes place just before an expression in the where clause:

/*+ES: INDEX(<index-list>) ANALYZER(<analyzer>) OPERATOR(<operator>) */ <expression><op><expression>

Where:

Here are some examples of NXQL queries using Elasticsearch hints:

-- Use an explicit Elasticsearch field
SELECT * FROM Document WHERE /*+ES: INDEX(dc:title.ngram) */ dc:title = 'foo'

-- Use fulltext search on an explicit list of fields, boosting dc:title
SELECT * FROM Document WHERE /*+ES: INDEX(dc:title.fulltext^3,dc:description.fulltext) */ ecm:fulltext = 'foo'

-- Use ES operators not present in NXQL
SELECT * FROM Document WHERE /*+ES: OPERATOR(regex) */ dc:title = 's.*y'
SELECT * FROM Document WHERE /*+ES: OPERATOR(fuzzy) */ dc:title = 'zorkspaces'
SELECT * FROM Document WHERE /*+ES: OPERATOR(common) */ dc:title = 'this is bonsai cool'

-- Use default ES simple_query_string search on dc:title.fulltext field
SELECT * FROM Document WHERE /*+ES: INDEX(dc:title.fulltext) OPERATOR(simple_query_string) */ dc:title = '\"fried eggs\" +(eggplant | potato) -frittata'

-- Use ES query_string on multiple fields using a fulltext analyzer
SELECT * FROM Document WHERE /*+ES: INDEX(dc:title.fulltext,dc:description.fulltext) ANALYZER(fulltext) OPERATOR(query_string) */ dc:title = 'this AND that OR thus'

-- Use ES for GeoQuery based on geo_bounding location in a bounding box; first param is top_left, followed by bottom_right point
SELECT * FROM Document WHERE /*+ES: OPERATOR(geo_bounding_box) */ osm:location IN ('40.73, -74.1', '40.01, -71.12')

-- Use ES for GeoQuery based on geo_hash_cell location near a point using geohash; examples below use level 5 precision (approximately 5kmx5km)
SELECT * FROM Document WHERE /*+ES: OPERATOR(geo_hash_cell) */ osm:location IN ('40.73, -74.1', '5')
-- Note that the location can use geohash instead of latitude & longitude
SELECT * FROM Document WHERE /*+ES: OPERATOR(geo_hash_cell) */ osm:location IN ('drm3btev3e86', '5')
NXQL Related Topics