2023:CMISQL Query

This article is about an older version of Grooper. Information may be out of date and UI elements may have changed.
2025	2023	2021

A CMISQL Query (aka CMIS Query) is Grooper's way of searching for documents in CMIS Repositories. Commonly, CMISQL Queries are used by Import Query Results to import documents from a CMIS Repository. CMISQL Queries are also used by CMIS Lookup to lookup data from a CMIS Repository. CMISQL Queries are based on a subset of the SQL-92 syntax for querying databases, with some specialized extensions added to support querying CMIS sources.

• CMISQL Queries are configured using the "CMIS Query" property found in "Import Query Results" and "CMIS Lookup".

The querying language used by CMISQL Queries is based on a subset of the SQL-92 grammar. Where SQL is used to search and select data from a database, CMISQL is used to search and select documents (and their metadata properties) from a storage location, represented in Grooper by a CMIS Repository.

Because the property used to configure a CMISQL Query is always named "CMIS Query", CMISQL Queries are often referred to simply as CMIS Queries.

About

CMIS Queries use clauses to retrieve content from a CMIS Repository based on metadata property values, much like a SQL query retrieves rows from a database based on column values. In both cases, a SELECT clause defines what is returned, and a WHERE clause defines the conditions used to filter results.

In general, a CMISQL statement takes the following form:

SELECT * FROM <Content Type> WHERE <Conditions> ORDER BY <Sort Criteria>

For example, to retrieve all email messages in the Inbox of an Exchange CMIS Repository from a specific sender, ordered by the date received (oldest first), you could use the following query:

SELECT * FROM Message WHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox')) ORDER BY DateTimeReceived ASC

The sections below break down each clause in more detail.

Clause Information

SELECT

Example CMISQL Query:

SELECT * FROM Message WHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox')) ORDER BY DateTimeReceived ASC

The SELECT clause defines which metadata properties are returned with each query result. These properties depend on the content type specified in the FROM clause.

From a practical standpoint, CMIS Queries return documents, not individual metadata values. Because the entire document (along with all metadata) is retrieved, CMIS Queries used for importing documents will always use SELECT *.

You will always begin an import-based CMIS Query with SELECT *.

FYI

When would you use something other than SELECT *? This occurs when using CMIS Lookups. A CMIS Lookup functions similarly to a database lookup, querying metadata values rather than retrieving documents. CMIS Queries used for importing content are the primary focus of this article. For more information, see the CMIS Lookup article.

FROM

Example CMISQL Query:

SELECT * FROM Message WHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox')) ORDER BY DateTimeReceived ASC

The FROM clause determines the content type being queried, such as documents or folders.

• The selected content type determines which metadata properties are available for use in the WHERE clause.

Available content types depend on the CMIS Repository and its CMIS Binding (connection type).

• Example: The Exchange binding exposes a Message content type with properties such as Subject, Sender, and DateTimeSent.
• Example: The SharePoint binding exposes document library content types, including custom metadata columns.

In this example, the query targets Exchange email messages by specifying FROM Message.

WHERE

Example CMISQL Query:

SELECT * FROM Message WHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox')) ORDER BY DateTimeReceived ASC

The WHERE clause defines the conditions that must be met for a document to be returned.

• Conditions are defined using predicates.
• Multiple predicates can be combined using AND or OR.
• Parentheses control logical precedence.
• Predicates can be negated using the NOT operator.

Where Predicates

A Where Predicate represents a logical condition applied to a CMISQL query’s WHERE clause.

Predicate	Description	Example
Comparison Predicate	Specifies search criteria by comparing a property value using operators, such as =, <, >, LIKE, or IS. Operator support varies by property and CMIS Binding. Example: The Exchange binding's "Subject" property cannot use the = operator. The LIKE predicate must be used instead.	DateTimeCreated < '12/31/2007'
IN Predicate	Specifies search criteria for an individual property, using a list of allowed values.	FileExtension IN ('.pdf', '.docx', '.xlsx')
CONTIAINS Predicate	Performs a full-text search. You can use AND, OR and NOT operators when crafting search parameters. Note: Only platforms that support full text searching are compatible with with the Contains Predicate.	CONTAINS('mortgage AND payment AND NOT vehicle')
MATCHES Predicate	Defines filtering criteria for a string property, where the property value must match a regular expression.	cmis:name MATCHES '^[A-Z]{3}-\d{4}$'
Scope Predicate	Restricts the search scope to the direct children (IN_FOLDER) or all descendants (IN_TREE) of a specific folder. Note: The NOT operator cannot be used with the Scope Predicate.	IN_FOLDER('/Documents/Grooper')
AT_LEVEL Predicate	Matches objects at a specific level below the base folder.	AT_LEVEL(2)
Predicate List	Groups multiple predicates using AND or OR. Allows multiple search conditions in a single CMISQL query.	(Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox/'))

• Note: Predicate support varies by CMIS Binding.
  • Example: The SharePoint binding does not support the Scope Predicate's IN_FOLDER option. IN_TREE must be used instead.
  • Example: The NTFS binding will not support the CONTAINS() predicate unless Windows Search indexing is enabled.

In the example queryWHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox')), a Predicate List combines:

• A Comparison Predicate (Sender)
• A Scope Predicate (Inbox folder)

ORDER BY

Example CMISQL Query:

SELECT * FROM Message WHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox')) ORDER BY DateTimeReceived ASC

The optional ORDER BY clause defines the sort order of query results.

• Multiple properties may be specified using a comma-separated list.
• ASC (ascending) and DESC (descending) are supported.
• Ascending order is the default, but explicitly specifying it is recommended.

CMISQL Query Editor

If you are unfamiliar with CMISQL syntax, the CMIS Query Editor allows you to build queries using a property grid rather than writing them manually.

The editor is available in:

1. The Import Query Results provider
2. A CMIS Repository via the "Search" tab

⚠	The following CMIS Bindings cannot be queried using CMISQL: FTP SFTP NTFS (unless indexed by Windows Search) These bindings are not compatible with the Import Query Results provider.

How to Access the CMIS Query Editor

There are two locations where the CMIS Query Editor can be accessed:

• From the Import Query Results provider when editing its CMIS Query. This allows users to filter imported documents based on the results of a CMISQL query.
• From a CMIS Repository's "Search" tab. This allows users to test CMISQL queries independently of the Import Query Results provider.

Note:' How to use the CMIS Query Editor is demonstrated in the next section.

Import Query Results > CMIS Query

Most commonly, you will use a CMISQL query to import documents using the Import Query Results provider.

FYI	The Import Query Results provider is one of two Import Providers that use a CMIS Connection to bring document content into Grooper. For more information, see the CMIS Import article.

When configuring the import settings, select the CMIS Repository to query using the Repository property. Select the CMIS Query property. Note: Some CMIS Bindings are not CMISQL queryable (for example, FTP and SFTP). If the CMIS Repository is not queryable, the CMIS Query property will not be available. Click the ellipsis button at the end of the property to open the CMIS Query Editor.
This is the CMIS Query Editor. Using this interface, you can craft a CMISQL query in one of two ways: Use the property grid to configure the clauses of the query. Free-type the query directly in the text editor.

Note: How to use the CMIS Query Editor is demonstrated in the next section.

CMIS Repository > Search Tab

You can also access the CMIS Query Editor when searching for documents in a CMIS Repository using the "Search" tab.

Select the CMIS Repository in the Node Tree. Navigate to the "Search" tab. Note: Some CMIS Bindings are not CMISQL queryable (for example, FTP and SFTP). If the CMIS Repository is not queryable, the "Search" tab will not be available. Use the property grid to configure the clauses of the query. Free-type the query directly in the text editor.

Note:' How to use the CMIS Query Editor is demonstrated in the next section.

How to Use the CMIS Query Editor

Next, we will demonstrate how to use the CMIS Query Editor to filter email messages based on specific criteria. The CMIS Query Editor interface allows you to construct a CMISQL query using metadata exposed by the selected CMIS Binding.

In this example, we will use the Exchange binding, which exposes queryable metadata for email messages such as subject, sender, and received date. Our query will import email messages that meet the following conditions:

1. Only email messages are imported (excluding other Exchange content such as appointments or tasks).
2. Only messages located in a specific folder are imported.
3. Only messages containing specific text search terms are imported.
4. Only messages from a specific sender are imported.
5. Only unread messages are imported.

This walkthrough covers the fundamentals of building a CMISQL query using the CMIS Query Editor.

The SELECT and FROM ClausesThe WHERE ClauseAdding Search PredicatesThe ORDER BY Clause and Testing the Query

The SELECT and FROM Clauses The property grid is divided into sections that correspond to the clauses of the CMISQL query. Adjusting these properties automatically updates the query text. The Select property controls the SELECT clause. By default, this property is set to *. This produces the opening of the query: SELECT *. The From property controls the FROM clause. Each CMIS Binding defaults to a specific content type. For the Exchange binding, the default content type is Item. An Item is a generic Exchange object and may represent messages, appointments, tasks, or notes. To query email-specific metadata, we must change the content type to Message.

Changing Content Types in the FROM Clause From this point forward, we will continue using the property grid to edit the query. Select the From property. Use the dropdown list to select Message. This restricts the query to email messages and exposes message-specific metadata. The CMISQL query text is updated automatically. SELECT * FROM Item becomes SELECT * FROM Message. As additional properties are configured, the query text will continue to update automatically. This allows you to build a CMISQL query without manually typing it. You may also freely edit the query text directly if preferred. You can switch back and forth between the property grid and the text editor at any time.

The WHERE Clause Next, we will configure the Where property to define the filter conditions for our query. Select the Where property. Choose the desired predicate type from the dropdown list. When filtering on multiple criteria, always select Predicate Collection. In this example, we will use both Comparison Predicates and a Scope Predicate.
Using Predicate Collection for Multiple Conditions Expand the Where sub-properties. The default Logical Operator is AND. This is appropriate here, as all conditions must be met. The OR operator is also available and returns results that match any predicate. Use the Expressions property to add predicates. Click the ellipsis button to open the Where Predicate Expressions editor.

Adding Search Predicates The Where Predicate Expressions editor is used to define individual search conditions. Click Add to create a new predicate. Begin by adding a Scope Predicate to limit the query to a specific folder.
The Scope Predicate The Scope Predicate restricts the query to a specific folder. Select the Search Scope property and click the ellipsis button. Choose a folder from the CMIS Repository’s folder hierarchy. In this example, the scope is restricted to the Wiki folder. Click OK to confirm. FYI If Include Subfolders is False, the IN_FOLDER() predicate is used, limiting results to the selected folder only. If Include Subfolders is True, the IN_TREE() predicate is used, including the selected folder and all subfolders.
The Contains Predicate If the connected CMIS Repository supports full-text search, you can use the Contains Predicate to search for specific text. In this example, we will retrieve email messages containing the phrase Wiki Vitals. To add a Contains Predicate: Press the Add button and select Contains Predicate This adds a new Contains Predicate to our list of predicates Using the Search String property, enter the text you would like to search for. In our case, only messages with the text Wiki Vitals somewhere in the subject line or email body or otherwise in the message's metadata will be retrieved. Notice Grooper automatically generates the property syntax for the query, CONTAINS('Wiki AND Vitals'). ⚠ Only platforms that support full-text searching can use the Contains Predicate.
The NOT Operator Any predicate can be logically negated using the NOT operator. In this example, monthly reports should be excluded. These emails contain the phrase Monthly Report. To logically negate a predicate: Add the predicate using the Add button. We've added another Contains Predicate. Configure the predicate's search parameters. What do we not want to retrieve in this case? Messages with the text Monthly Report somewhere in their body. So, we've configured the Search String property to search for that term. Change the Not property to True. This will negate the predicate's logic. If the email contains the search term, it will not be retrieved.
The Comparison Predicate The Comparison Predicate filters documents using metadata values and comparison operators such as =, >, or <. In this example, we will only import unread messages. Press the Add button and select Comparison Predicate This adds a new Comparison Predicate to our list of predicates Using the Property Name property, select a property from a list of the documents' available metadata properties. What metadata properties are available will be determined by the CMIS Repository's binding/connection type and the content type selected in the FROM clause. We've selected the IsRead property. Using the Comparison Operator select which operator you want to use for comparison. Depending on the value's type (string, decimal, Boolean, etc.) different comparison operators may be available. Here, we are checking for equivalency using the = operator. Using the Search Value property, enter the control variable you want to use for comparison. In our case, we want to check if these emails have not been read, indicated by the email message's "IsRead" property being False.
Example: Searching by "Sender" using the LIKE operator The LIKE operator is used to match substrings in string values. To filter by sender: Add a Comparison Predicate. Select Sender as the Property Name. Choose LIKE as the operator. Enter %cdearner@bisok.com% as the search value.
FYI The LIKE operator matches substrings using wildcards: % matches zero or more characters. _ matches exactly one character. A LIKE expression without wildcards behaves like =. Examples: "dog_" matches "dog" and "dogs" "%dog" matches "dog" and "catdog" Some CMIS Bindings have limited LIKE support and may restrict wildcard usage.
Finish Adding Predicates Click OK to close the predicate editor.
When complete, the predicates form the conditions of the query’s WHERE clause.

The ORDER BY Clause The optional ORDER BY clause controls the sort order of the query results. In this example, we will sort messages by sent date, with the most recent messages listed first. Select the Order By property and click the ellipsis button.
Click Add to add a sort field. Using the Property Name' property, select which document metadata property you want to sort by. Example: Select DateTimeSent as the Property Name. Using the Sort Direction property, select which direction you want to sort by. Choose ASC to sort in ascending order. Choose DESC to sort in descending order.
Testing the Query The completed query is validated automatically. Click Execute Query to test it. Results appear in the Query Results panel. Double-click a result to view it in the Document Preview panel.

Unsupported query configurations

NTFS

The NTFS connection binding will not support the CONTAINS() predicate (for full text searching) unless the Windows file system has been indexed by the Windows Search service.

SharePoint

The SharePoint connection binding does not support the IN_FOLDER predicate.

• You must use the IN_TREE predicate instead when selecting a folder location to query.
• Be aware the IN_TREE search is recursive where IN_FOLDER is not. This means any subfolders will be queried as well as the targeted folder.

The SharePoint connection binding has a hard limitation when it comes to querying larger repositories, with many thousands of folders and files.

• There is currently a threshold limit of 5000 rows.
• This means if a query has to scan more than 5000 items to return an item, SharePoint will return the following error:
  • "Internal Server Error (500): The attempted operation is prohibited because it exceeds the list view threshold."

Box

Search values are required for Box CMISQL Queries. You must include a WHERE clause with at least one valid search condition.

The Box API has some built in requirements to perform search queries. This is not something Grooper can get around. In the past, this has caused Grooper users a lot of confusion when configuring WHERE clauses in CMISQL search queries.

To avoid confusion the following properties are not queryable at this time:

• Created by (cmis:createdBy)
• Modified by (cmis:lastModifiedBy)

The Box binding does not support does not support the IN_FOLDER predicate.

• You must use the IN_TREE predicate instead when selecting a folder location to query.
• Be aware the IN_TREE search is recursive where IN_FOLDER is not. This means any subfolders will be queried as well as the targeted folder.

Box has some documented limitations in its CMIS implementation in general. In most cases, Grooper is able to circumvent these issues, but not in all cases. Grooper is only able to return what Box gives us when executing a CMISQL query.

• Documents are not immediately queryable after their metadata templates have been edited in Box. If you have just added/edited fields in a Box document's metadata template, there is some lag time between when Grooper can query that data. You may need to wait several hours before a CMISQL query will return the document.
• We have encountered sporadic issues when using aliases (i.e. SELECT propertyName AS 'AliasName') in a Box CMIS Lookup query. As such, CMIS Lookups used to populate Grooper fields may be unreliable. However, please continue to report this issue to the support team at support@bisok.com. Additional reports will help the QA team diagnose this issue and help the Development team implement a solution, if possible.

Exchange

When configuring a WHERE clause (using a Comparison Predicate), the "Subject", "Sender", "To Recipients", "Cc Recipients", and "Bcc Recipients" cannot use the = operator.

• Use the LIKE operator instead.
  • Incorrect syntax: Sender = '%user@example.com%'
  • Correct syntax: Sender LIKE '%user@example.com%'
• BE AWARE: EWS only supports substring matching for these properties. You must use the % wildcard on either side of the search term when using the LIKE operator.
  • Incorrect syntax: Sender LIKE 'user@example.com'
  • Correct syntax: Sender LIKE '%user@example.com%'

IMAP

Gmail and other email clients does not support the IN_FOLDER or IN_TREE predicates because these are not part of the standard IMAP protocol.

• Instead Gmail uses "labels" which are not searchable by Grooper's querying mechanism.
• Some email clients do support these search predicates. We do not have a complete list of providers that do and do not support IN_FOLDER and IN_TREE at this time.

Object Model info

Type name: Grooper.CMIS.CmisQuery

Inheritance

Grooper Object (Grooper.GrooperObject)

Connected Object (Grooper.ConnectedObject)

Embedded Object (Grooper.EmbeddedObject)

CMISQL Element (Grooper.CMIS.QueryElement)

CMISQL Query (Grooper.CMIS.CmisQuery)

Derived Types

CMISQL Query is the Grooper object that selects documents or folders from a CMIS Repository.

• CMISQL Query has no derived types.
• However, several sibling types are key to its composition and use in Grooper.

CMISQL Element (Grooper.CMIS.QueryElement)

CMISQL Query (Grooper.CMIS.CmisQuery)

Join Clause (Grooper.CMIS.JoinClause) - Used to create the JOIN clause of a CMISQL Query

ORDER BY Element (Grooper.CMIS.OrderByElement) - Used to create the ORDER BY clause of a CMISQL Query

Select Element (Grooper.CMIS.SelectElement) - Used to create the SELECT clause of a CMISQL Query

Where Predicate (Grooper.CMIS.WherePredicate) - Used to create the WHERE clause of a CMISQL Query. Its derived types expose search conditions that can be added to the WHERE clause.

AT_LEVEL Predicate (Grooper.CMIS.AtLevelPredicate) - Matches documents/folders at a specific level below the base folder.

Comparison Predicate (Grooper.CMIS.ComparisonPredicate) - Specifies search criteria for an individual property using a comparison operator (=, <, >, LIKE etc)

CONTAINS Predicate (Grooper.CMIS.ContainsPredicate) - Specifies full-text search criteria

IN Predicate (Grooper.CMIS.InPredicate) - Specifies search criteria for an individual property, using a list of allowed values.

MATCHES Predicate (Grooper.CMIS.MatchesPredicate) - Specifies filtering criteria for a string property, where the property value must match a regular expression.

Predicate List (Grooper.CMIS.PredicateCollection) - Defines a collection of search conditions separated by logical AND/OR operators.

Scope Predicate (Grooper.CMIS.ScopePredicate) - Restricts the search scope to the direct children (IN_FOLDER) or all descendants (IN_TREE) of a folder.