[POST] /search
The /objects/search endpoint is the central endpoint for searching objects inside the enaio® ECM system. The query language based on CMIS serves to standardize DMS requests to the enaio® ECM system.
The CMIS query language consists of a subset of the SQL-92 grammar, augmented by a syntax for requesting full text. Thus, the CMIS query language provides a relational view of the data. The virtual tables requested in the FROM clause correspond to DMS object types and the virtual columns requested in the SELECT clause correspond to the CMIS properties (index data fields).
- 1 1. /api/dms/objects/search Endpoint
- 2 2. Properties
- 3 3. Query Language Definition
- 3.1 3.1 Simple Select Queries
- 3.2 3.2 Queries with Aliases
- 3.3 3.3 Queries with Conditions
- 3.4 3.4 Queries for Full-Text
- 3.4.1 3.4.1 Full-Text on the entire System
- 3.4.2 3.4.2 Full-Text on certain object types
- 3.4.3 3.4.3 Full-Text query with Parent-Objects condition
- 3.4.4 3.4.4 Full-Text query with Object Id condition
- 3.4.5 3.4.5 Full-Text query condition
- 3.4.6 3.4.6 Full-Text queries with facets
- 3.4.7 3.4.7 Full-Text queries on a Field
- 3.4.8 3.4.8 Full-Text Automplete Suggestions
- 3.5 3.5 Queries with Sorting
- 3.6 3.6 Queries with Aggregations
- 3.7 3.7 Queries with Date Functions
- 3.8 3.8 Queries on contentstream Fields
- 3.9 3.9 Queries with System Fields
- 3.10 3.10 Queries with Parameters
- 3.11 3.11 Queries for Object History/Audit Logs
- 3.12 3.12 Queries for User Trays
- 3.13 3.13 Queries for Locked (Checked-out) Documents
- 3.14 3.14 Queries for Deleted Objects in the Recycle Bin
- 3.15 3.15 Queries for Parent and Child Object Types
- 3.16 3.16 Queries for Multiple Object Types
- 3.17 3.17 Queries for Multiple Independent Object Types
- 3.18 3.18 Queries for Favourites
1. /api/dms/objects/search Endpoint
Description | Search for objects in the ECM system. |
|---|---|
Request | POST |
Request | Content-Type: application/json |
Response | JSON |
Response | Success: HTTP 200 |
Minimum Version | 3.0.0 |
Changelog |
|
Example | {
"query" : {
"statement" : "SELECT * FROM sysemail WHERE email = @emailAddress AND sysfrom IN @from AND CONTAINS(@text)",
"skipCount" : 0,
"maxItems" : 50,
"handleDeletedDocuments" : "DELETED_DOCUMENTS_EXCLUDE",
"options": {
"Rights": 1,
"ExportDepth": 1,
"RegisterContext": 1
},
"parameters": {
"emailAddress": "info@optimal-systems.de",
"text": "Meeting",
"from": "('hallo@huhu.de','info@huhu.de')"
}
}
}It may be the case, that the above example will not work out of the box and result in a JSON parse error. To fix this, replace the spaces at the beginning of each line with tabs in an editor of your choice. This also applies to all following JSON code examples. |
Request | {
"query" : {
"statement" : "SELECT * FROM sysemail WHERE email = @emailAddress AND sysfrom IN @from AND CONTAINS(@text)",
"skipCount" : 0,
"maxItems" : 50,
"handleDeletedDocuments" : "DELETED_DOCUMENTS_EXCLUDE",
"nativeOptions": {
"includePermissions": true,
"exportDepth": 1,
"registerContext": true
},
"parameters": {
"emailAddress": "info@optimal-systems.de",
"text": "Meeting",
"from": "('hallo@huhu.de','info@huhu.de')"
}
}
}It may be the case, that the above example will not work out of the box and result in a JSON parse error. |
Result |
2. Properties
The JSON request has the following properties:
Property | Description |
|---|---|
| This property contains the search statement. A description can be found on this page. |
| This property is used to tell the ECM server at which item the result list should be started if the user wants to page the result list. |
| This property is used to tell the ECM server how many objects should be included in the result list, starting from the |
| This property can take one of the following string constants:
|
| This property is used to tell the ECM server to load the current user's rights for each result object. The default value is |
options | This map contains platform specific options for the search. See the following page for search options. |
| This map contains all placeholder parameters of the statement without the @ character. |
3. Query Language Definition
A query is structured as follows:
Query | SELECT | [virtual column list] | FROM | [virtual table names] | WHERE | [conditions] | ORDER BY | [sorting specification] |
|---|---|---|---|---|---|---|---|---|
Description |
| list of result fields |
| object type list |
| limitations |
| sorting criteria |
Important: Each field or object type of the object definition is referred to by its internal name. The query language definition does not support display names, db names or GUIDs.
3.1 Simple Select Queries
The query syntax is similar to SQL
"SELECT * FROM email"Select the fields with the internal names "kundennr" and "datum" from all objects of the object type with internal name rechnung.
Please note if the internal name of a field is identical to a keyword of the Query-Syntax like “from”, “asc”, “desc” then a alias for the object type in the from clause must be defined and the fields must be queried within the alias (e.g. d.from, d.asc, d.desc).
"SELECT kundennr, datum FROM rechnung"It is not possible to use field names which are identical to key words of the query like e.g. “select“, “from”, “where”, “order”, “asc”, “desc”. Then the query will fail. To query such field names alias for the object type is necessary like “d.select”
3.2 Queries with Aliases
3.2.1 Alias for selected fields
Properties can be queried with an alias name. In the results, the properties' values are returned with their alias name. The alias can be specified separated by a space behind the property name or using the keyword AS between the property name and alias name.
The result json will show the properties k and d instead of kundennr and datum
"SELECT kundennr k, datum d FROM rechnung"The same as in the example above but queried with the AS keyword
"SELECT kundennr AS k, datum AS d FROM rechnung"3.2.2 Alias for object types
Object types can have a alias to shorten the query in case multiple object types are queried or field names are identical to key words of the query.
"SELECT r.kundennr, r.datum FROM rechnung r"In the example above the object type “rechnung” is shorten by the alias “r”.
3.3 Queries with Conditions
In the WHERE clause, conditions can be defined and combined with logical operators. Priority-wise, the AND operator has a higher binding than the OR operator. Parentheses can be used to control the binding manually as shown in the following examples.
In our examples, we use the following placeholder syntax: field = @placeholder. This is more error-prone and the DMS service will make sure to correctly place the condition values. We strongly advise you to also use this placeholder syntax and not insert the condition values directly into the statement. For more information regarding the placeholder syntax, see chapter 3.10 on this page.
The following operators are supported:
comparison operators: <, >, <=, >=, <>, =
join operators: AND, OR, NOT
additional predicates: LIKE, IN, IS, NULL
Select all objects with all fields of type "rechnung" where the value of the field "betrag" is greater than 100
"SELECT * FROM rechnung WHERE betrag > 100""SELECT * FROM rechnung WHERE betrag > 100 AND lieferant = 'OPTIMAL SYSTEMS GmbH'""SELECT * FROM rechnung WHERE (betrag > 100 OR betrag < 10) AND lieferant IN ('OPTIMAL SYSTEMS GmbH')"The parentheses surrounding the first two conditions result in a higher binding of the OR operator. Without the parentheses the condition would be:
WHERE betrag > @betragGreater100 OR (betrag < @betragSmaller10 AND lieferant IN (@lieferanten))
"SELECT * FROM rechnung WHERE lieferant NOT IN ('OPTIMAL SYSTEMS GmbH')""SELECT * FROM rechnung WHERE lieferant IS NULL"The NULL operator in a condition checks whether a field value is empty or not set (null). If the search condition is supposed to check whether a certain field is empty, then the query should check for null instead of an empty string.
"SELECT * FROM rechnung WHERE lieferant LIKE 'OPTIMAL SYSTEMS GmbH'"Wildcard characters are used with the LIKE operator. There are two wildcards used in conjunction with the LIKE operator:
%– The percentage sign represents zero, one, or multiple characters_– The underscore represents a single character
For normal equal-conditions the following placeholder are available:
* – Various characters
? – One various character
~ – Phonetic search
3.3.1 Queries with Table-Field Conditions
Next to conditions on normal Indexdata Mask fields there could since Version 5.4.0 also be conditions on table field columns. In enaio there is only the possibility to set conditions on a whole table control column and not on a specific cell which means column and row. Because of that all examples will show a * as the row number which stands for all rows. A numeric value instead of a * will result in an error. Table field column conditions can also be joined with AND and OR to a more complex condition. The following examples will show table field conditions in theory and explain the different parts.
"SELECT * FROM Aufenthalt WHERE <tableFieldInternalName>[*].<columnNameInternalName1> <operand> <value>"
"SELECT * FROM Aufenthalt WHERE <tableFieldInternalName>[*].(<columnNameInternalName1> <operand1> <value1> AND/OR <columnNameInternalName2> <operand2> <value2>)"The first statement defines with <tableFieldInternalName> the internal name of the table control on the index data mask. Then a always static part is following with the star inside two sqare brakets. Afterwards a point follows, which is the divider between the table control itself and the column notation. The column notation is also the internal name of each column to put a condition onto followed by the operand mentioned in the previous chapter 3.3 and the condition value. If there should be multiple conditions on the different columns the conditions can be joined with AND and OR. There is also a shortcut writing form for the column notation. After the point, which divides the columns from the table control as previous written, there could be written a round braket open and inside the finally round braket colsing column conditions can be written with AND/OR joining without writing always the table control name every time. This form of writing is shown in line two of the example above.
A more complex example is shown afterwards.
"SELECT * FROM Aufenthalt WHERE Relocations[*].((betrag > 100 OR betrag < 10) AND lieferant IN ('OPTIMAL SYSTEMS GmbH', 'Deutsche Bahn AG') AND datum IS NULL)"Here the shortcut writing form is choosen on the table control field Relocations with numeric conditions on the column betrag and a IN-Condition on the column lieferant. Finally as Null-Condition, which means there should be no value in the column cell itself, is set on the column datum. Important is, that all conditions must be met on one single row. The condition is not true condition if the first condition is valid on row 1 and the other conditions are not valid on row 1 but on row 3.
Table field conditions can also be mixed with normal field conditions and even with Full-Text conditions.
3.3.2 Queries with Multilingual Catalogue Conditions
Starting with enaio® 11.0 and DMS service for enaio® 6.0.0, it is possible to query multilingual catalogues within the query. To add the locale to the condition, it is necessary to use the parameter syntax for conditions as described in chapter 3.10. With the parameter syntax, an object can be specified that contains a value and a catalogueQueryLocale as shown in the following example. This is aslo possible for table field conditions.
{
"query": {
"statement": "SELECT * FROM Aufenthalt WHERE myMultilingualCatalog = @multilingualCatalog AND Relocations[*].myMultilingualCatalogueColumn = @multilingualCatalogueColumn",
"skipCount": 0,
"maxItems": 1000,
"handleDeletedDocuments": "DELETED_DOCUMENTS_EXCLUDE",
"options": {
"OutputCatalogueLocale": "fr-FR"
},
"parameters": {
"multilingualCatalog": {
"value": "Anästhäsie",
"queryCatalogueLocale": "de-DE"
},
"multilingualCatalogueColumn": {
"value": "Surgery",
"queryCatalogueLocale": "en-US"
}
}
}
}The example also shows how to specify the query result locale with the OutputCatalogueLocale option.
3.4 Queries for Full-Text
Full-text queries can be carried out on one specific object type or the entire system. A full-text condition has to be placed within a CONTAINS in the WHERE clause.
"SELECT * FROM rechnung WHERE CONTAINS('my fulltext search words')""SELECT * FROM rechnung WHERE betrag > 100 AND CONTAINS('my fulltext search words')"The two examples above show a full-text condition on a specific object type. The CONTAINS clause here can either be used within an AND condition, or by itself without any other conditions, because enaio® server handles this condition separately from all other conditions specified. Also, a full-text condition will only be applied to the first object type of the query and cannot be used for queries for parent and children object types and queries for multiple object types which contain multiple object types. Union queries for queries for multiple independent object types are not affected by this restriction because they operate on independent queries.
3.4.1 Full-Text on the entire System
A full-text search could also be run since Version 5.4.0 for the entire system using the virtual object type system:object in the FROM part. This virtual object type cannot be joined with other object types and can only be executed on its own. In this case, the query is redirected to the enaio® full-text engine and not executed on the normal database.
"SELECT * FROM system:object WHERE CONTAINS('my fulltext search words')"Full-text queries on the entire System are limitted to return only the full-text information without any index data information of the found objects. To query the index data of the found objects, a query for multiple independent object types can be performed afterwards. The full-text information can be queried all within the * operator or dedicated if not everything should be loaded. The following table describe the dedicated fields, which can be queried within a system-wide full-text query:
Field | Description |
|---|---|
| All of the following fields are included in the result. |
| The object ID is included in the result. |
| The object type ID is included in the result. |
| The |
While system:objectId and system:objectTypeId are well known, the SCORE() function can be used to retrieve the information on how relevant a returned result is with regards to the full-text expression send inside CONTAINS of the WHERE clause. If it is included, each result object contains a SEARCH_SCORE parameter which has a value between 0 and 1.0. The system-wide full-text result is always sorted descending with regards to this score, where 1.0 is the best and 0 is equal to being nearly not relevant to the CONTAINS value.
To get better the full-text search results (system wide or bound to one object type), the following placeholder characters can be used:
Placeholder | Description |
|---|---|
* | a random string |
? | one random character |
~ | phonetic search |
Elasticsearch supports more than the above mentioned operands. Please refer to the Elasticsearch documentation for more information.
3.4.2 Full-Text on certain object types
To limit the system wide full-text query to certain object types, a second condition on system:objectTypeId can be appended which accepts numeric object type IDs or the internal names of object types on which the full-text query is to be executed.
"SELECT * FROM system:object WHERE CONTAINS('my fulltext search words') AND system:objectTypeId IN (5, 55, 648852)""SELECT * FROM system:object WHERE CONTAINS('my fulltext search words') AND system:objectTypeId IN ('internalName1', 'internalName2', 'internameName3')"A condition on system:objectTypeId can have the following operators: =, <>, IN, and NOT IN. All other operators like greater, greater-equal, less, or less-equal are not supported at the moment.
3.4.3 Full-Text query with Parent-Objects condition
Since Version 6.4.0 it is possible to limit the system wide full-text query also to certain folders or registers. The result will include then only those objects which fit to the searched full-text phrase and which are located inside the given folders or registers. The following example will show how to limit a full-text query to certain parent objects:
"SELECT * FROM system:object WHERE CONTAINS('my fulltext search words') AND system:parentId = 965632""SELECT * FROM system:object WHERE CONTAINS('my fulltext search words') AND system:parentId IN (222365, 965632)"The limitation is only possible with the two operands = and IN. All other operands will be rejected.
3.4.4 Full-Text query with Object Id condition
Since Version 6.5.0 it is possible to limit the system wide full-text query also to on a predefined list of objects. The result will include then only those objects which fit to the searched full-text phrase. The following example will show how to limit a full-text query to objects:
"SELECT * FROM system:object WHERE CONTAINS('my fulltext search words') AND system:objectId = 40393""SELECT * FROM system:object WHERE CONTAINS('my fulltext search words') AND system:objectId IN (40393, 40865)"The limitation is only possible with the two operands = and IN. All other operands will be rejected.
3.4.5 Full-Text query condition
Since Version 6.5.0, with the parameter syntax, an object can be specified that contains a value and a queryCatalogueLocale to specify the condition locale, as shown in the following example.
{
"query": {
"statement": "SELECT * FROM kataloge WHERE MehrsprachigeListe = @MehrsprachigeListe AND CONTAINS(@myFullTextValue)",
"parameters": {
"MehrsprachigeListe": {
"value": "Apfel",
"queryCatalogueLocale": "de_DE"
},
"myFullTextValue": {
"value": "Hello",
"queryCatalogueLocale": "en_US"
}
}
}
} 3.4.6 Full-Text queries with facets
Since Version 6.4.0 it is possible to limit the system wide full-text query with facet restrictions. The default facets are:
system:createdBysystem:creationDatesystem:lastModifiedBysystem:lastModificationDatemimetypegroup
Next to theese default facets there can be defined in the full text within the aggregations-cfg.json additional custom facets. They could then be queried within their name and possible values.
The system fields system:createdBy and system:lastModifiedBy accept a Username or a list of Usernames. Differently to the normal system field documentation the dateTime system fields system:creationDate and system:lastModificationDate accept no iso date time string. They are accepting the defined constants from the full text. In default this are:
thismonth()
lastmonth()
thisyear()
lastyear()
older()
The functions are transfered without their final brackets: thismonth() will be send as thismonth to the full text. Therefore also other time methods can be written for this system fields if they are defined in the full text. For the other facets like mimetypegroup or custom facets the values are transferrd exactly as they are written in the query. For facets only the operands = and IN are allowed and all facet restrictions must be joined with an AND condition. OR-Conditions are not supported.
"SELECT * FROM system:object WHERE CONTAINS('my fulltext search words') AND system:createdBy IN ('DMS_API', 'DMS_API02') AND system:creationDate IN (thisyear(), lastyear()) AND system:lastModifiedBy IN ('DMS_API02', 'DMS_API') AND system:lastModificationDate IN (lastmonth(), thismonth()) AND mimetypegroup IN ('EXCEL', 'MAIL') AND CustomFacet IN ('MyFacet', 'MyFacet 2')"3.4.7 Full-Text queries on a Field
Full-Text conditions on explicit index data fields are not directly supported. There is since Version 5.4.0 the possibility to write the following query:
"SELECT * FROM rechnung WHERE betrag > @betrag AND company CONTAINS('OPTIMAL SYSTEMS GmbH')"or
"SELECT * FROM Aufenthalt WHERE Relocations[*].company CONTAINS('OPTIMAL SYSTEMS GmbH')"This kind of usage of the CONTAINS keyword will result in a LIKE condition with a star infront and behind the given Full-Text condition value. All entered special Full-Text characters for more weigth are send 1:1 to the enaio server and won't work as expected. Therefore only normal string conditions are allowed but not checked. This is possible on normal index data fields as well on table control columns.
3.4.8 Full-Text Automplete Suggestions
From Version 6.5.0 on Autocomplete suggestions can be queried with the following syntax:
"SELECT SUGGEST('<Term>') FROM system:object"The <Term> ist the String for which autocomplete suggestions should be loaded. The suggestions can also be limitted to certain object types with a condition ob system:objectTypeId as shown in capter 3.4.2.
3.5 Queries with Sorting
To sort the result list, use the ORDER BY clause.
"SELECT * FROM rechnung WHERE betrag > 100 ORDER BY betrag DESC""SELECT * FROM rechnung WHERE betrag > 100 ORDER BY betrag DESC, datum ASC"3.6 Queries with Aggregations
Difference to the yuuvis Core API
It is currently not possible to include aggregations in the enaio® Core API.
3.7 Queries with Date Functions
The query language provides some frequently used time span functions for WHERE clauses. These functions are translated into ranges where the start is at midnight and the end is at 23:59:59.
The today() function is the only one also available for TIME fields. If one of the other functions is used in combination with the field type TIME, an exception is thrown.
today()– a date field or date time field containing today's time range.yesterday()– a date field or date time field containing yesterday's date range.thisweek()– a date field or date time field containing this week's date range (a week starts on Monday and ends on Sunday).lastweek()– a date field or date time field containing last week's date range (a week starts on Monday and ends on Sunday).thismonth()– a date field or date time field containing this month's date range.lastmonth()– a date field or date time field containing last month's date range.thisyear()– a date field or date time field containing this year's date range.lastyear()– a date field or date time field containing last year's date range.
SELECT * FROM rechnung WHERE datum IN lastweek()SELECT * FROM rechnung WHERE datum <> lastyear()SELECT * FROM Aufenthalt WHERE Relocatons[*].datum < thisyear()3.8 Queries on contentstream Fields
In the enaio® Core API, it is currently not possible to query all contentstream fields as in yuuvis®. Some of them are queryable with their corresponding system field. For more information about querying system fields, refer to section 3.9. The following contentstream fields also exist in yuuvis® and can be queried as described:
Contentstream field | How to query in enaio® |
|---|---|
| Currently not queryable in enaio® Core API |
| Currently not queryable in enaio® Core API |
|
|
| Currently not queryable in enaio® Core API |
|
|
| Currently not queryable in enaio® Core API |
| Currently not queryable in enaio® Core API |
| Currently not queryable in enaio® Core API |
3.9 Queries with System Fields
Next to the index data fields, which are visible in different enaio® clients, an enaio® object has system fields that contain technical data for the object. Until DMS service version 5.4.x, some system fields were returned in the search result by default. It was possible to query system fields that were not returned by default explicitly by adding them to the field query as shown in the following example:
SELECT system:createdBy, system:creationDate FROM rechnung WHERE system:objectId IN (@objectIds)Another possibility was to send server options in the query. Based on those options, some system fields were returned in addition to the index data fields.
As of version 6.0.0, the DMS service for enaio® only returns three system fields by default:
system:objectIdsystem:objectTypeIdsystem:baseTypeId
All other system fields must be queried explicitly. System fields – except for the three mentioned ones – are also no longer included if the star operator for the selected fields is used. They can be queried by listing them next to the desiredcindex data fields as shown in the example above. As of version 6.0.0, it is also possible to query them within the systemFields option property as shown in the following two examples:
{
"query" : {
"statement" : "SELECT * FROM myObjectTypeInternalName",
"skipCount" : 0,
"maxItems" : 50,
"handleDeletedDocuments" : "DELETED_DOCUMENTS_EXCLUDE",
"options": {
"SystemFields": ["*"]
}
}
}{
"query" : {
"statement" : "SELECT * FROM myObjectTypeInternalName",
"skipCount" : 0,
"maxItems" : 50,
"handleDeletedDocuments" : "DELETED_DOCUMENTS_EXCLUDE",
"options": {
"SystemFields": [
"system:OBJECT_FLAGS",
"system:OBJECT_MAIN"
]
}
}
}In the first example above, all system fields of the objects for myObjectTypeInternalName are returned. This could be a long list according to Chapter 1.2. If only certain system fields are needed, it is also possible to query them by their names seperated with a semicolon.
3.10 Queries with Parameters
The JSON field parameters can be used to define parameter values that can be referenced by @<parameter> within a preformed SQL statement and replaced at runtime. Currently, there are two parameters reserved within the SQL query language:
@userId - The user ID of the current user is inserted as value
@userName - The user name of the current user is inserted as value (since version 3.1.0)
@userRoles - enaio® does not currently support this constant but may do so in the future.
These three reserved parameters cannot be used in the parameters block in the JSON below. If they are used anyway, an exception is thrown.
{
"query" : {
"statement" : "SELECT * from <ObjectTypeInternalName> WHERE email = @emailAddress AND email:from IN @from AND CONTAINS(@text)",
"parameters": {
"emailAddress": "info@optimal-systems.de",
"text": "Meeting",
"from": "('help@enaio.de','info@enaio.de')"
}
}
}For multilingual catalogue conditions the parameter value can be objects which contain value and queryCatalogueLocale children to specify the condition locale as shown in the next example:
{
"query": {
"statement": "SELECT * FROM Aufenthalt WHERE myMultilingualCatalog = @multilingualCatalog AND Relocations[*].myMultilingualCatalogueColumn = @multilingualCatalogueColumn",
"skipCount": 0,
"maxItems": 1000,
"handleDeletedDocuments": "DELETED_DOCUMENTS_EXCLUDE",
"options": {
"OutputCatalogueLocale": "fr_FR"
},
"parameters": {
"multilingualCatalog": {
"value": "Anästhäsie",
"queryCatalogueLocale": "de-DE"
},
"multilingualCatalogueColumn": {
"value": "Surgery",
"queryCatalogueLocale": "en_US"
}
}
}
}3.11 Queries for Object History/Audit Logs
With this search query it is possible to query the history/audit log of a DMS object. The properties returned can be defined or all fields can be queried like in the history endpoint. It is also possible to configure paging, and to request multiple object history/audit log request at the same time. The following examples show some of the possibilities:
SELECT * FROM system:audit WHERE system:objectId = @objectId
SELECT system:creationDate, detail FROM system:audit WHERE system:objectId = @objectId
SELECT * FROM system:audit WHERE system:objectId IN (@objectIds)
The first query queries all history/audit properties for a given objectId. In the second query only two properties are returned and the third one will return history/audit entries of multiple DMS objects. If multiple DMS objects are requested, the result contains a property for the objectId to identify which DMS object the entry belongs to. Paging and limitation is possible with skipCount and maxItems. If multiple objects are queried, the totalNumItems property does not return the total amount in any case because enaio® will stop loading history entries if the limit is reached. The hasMoreItems property is also not reliable in this case. No order can be applied. Conditions other than system:objectId cannot be used either.