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. `/api/dms/objects/search` Endpoint

Description	Search for objects in the ECM system.
Request Method	POST
Request Header	Content-Type: application/json
Response Format	JSON
Response Statuscodes	Success: HTTP 200
Minimum Version	3.0.0
Changelog	3.1.0: Queries for user tray added (3.12) 3.2.2: Queries for locked (checked-out) documents added (3.13) 5.1.0: Queries for children and/or parent fields added (3.14 & 3.15) 5.3.0: Queries for independent object types added (3.16) 5.4.0: Queries for system-wide full text added (3.4) 5.4.0: Queries for table control fields added (3.3.1 & 3.4.1) 6.0.0: `nativeOptions` replaced by `options` object 6.0.0: Support for multilingual catalogues 6.0.0: Introduced `systemFields` property in the `options` section (3.9) 6.0.0: Queries for favourites added (3.18) 6.4.0: Fulltext queries extended (3.4.3 & 3.4.6)
Example as of 6.0.0	`{ "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 Example Until 5.5.x	`{ "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. 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.
Result Example	See 1.3 Result Format

2. Properties

The JSON request has the following properties:

Property	Description

Property	Description
`statement`	This property contains the search statement. A description can be found on this page.
`skipCount`	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 not available for system-wide full-text queries. They are currently not pageable. Only `maxItems` can be extended step by step.
`maxItems`	This property is used to tell the ECM server how many objects should be included in the result list, starting from the `skipCount` value. If `skipCount` equals 50 and `maxItems` equals 150, then the ECM server will return the objects 51 to 200 in the result list.
`handleDeletedDocuments`	This property can take one of the following string constants: `DELETED_DOCUMENTS_INCLUDE` This property is currently not supported in enaio^® DMS Core API. `DELETED_DOCUMENTS_ONLY` Only objects from the recycle bin are listed in the results. `DELETED_DOCUMENTS_EXCLUDE` No objects from the recycle bin are listed in the results. This is the default.
`includePermissions`	This property is used to tell the ECM server to load the current user's rights for each result object. The default value is `false`. If this property is enabled, a permissions property will be added in the result JSON for each result object.
^options	This map contains platform specific options for the search. See the following page for search options.
`parameters`	This map contains all placeholder parameters of the statement without the @ character. We recommend to use these parameters instead of inserting the condition values directly into the statement.

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.

"SELECT kundennr, datum FROM rechnung"

3.2 Queries with Aliases

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

Field	Description
`*`	All of the following fields are included in the result.
`system:objectId`	The object ID is included in the result.
`system:objectTypeId`	The object type ID is included in the result.
`SCORE()`	The `SEARCH_SCORE` field is included in the result.

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

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:createdBy
system:creationDate
system:lastModifiedBy
system:lastModificationDate
mimetypegroup

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^®

Contentstream field	How to query in enaio^®
`contentStreamId`	Currently not queryable in enaio^® Core API
`repositoryId`	Currently not queryable in enaio^® Core API
`mimeType`	`system:OBJECT_MIMETYPEID` This is the internal ID of the mime type and not a string like "application/pdf". Querying the real mime type string is currently not supported.
`fileName`	Currently not queryable in enaio^® Core API
`length`	`system:OBJECT_FILESIZE`
`range`	Currently not queryable in enaio^® Core API
`digest`	Currently not queryable in enaio^® Core API
`archivePath`	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:objectId
system:objectTypeId
system: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.

3.12 Queries for User Trays

To load all objects from the curent user's user tray, the following query can be used. Currently, it is neither possible to add any conditions to the user tray query nor any result field limitations. Only paging with skipCount and maxItems is possible. To query the user tray, a DMS service version 3.1.0 or later is necessary.

SELECT * from system:tray

3.13 Queries for Locked (Checked-out) Documents

To load all locked (checked-out) documents of the current user, the following query can be used. Currently, it is neither possible to add any conditions to the query nor any result field limitations. Only paging with skipCount and maxItems is possible. For this query, a DMS service version 3.2.2 or later is necessary.

SELECT * from system:locked

3.14 Queries for Deleted Objects in the Recycle Bin

Objects in the recycle bin can be loaded with the following query. Note that the DMS service for enaio^® version 5.3.0 is required which is availble as of version 10.0.

SELECT * from system:trash

This query will only load the recycle bin objects of your personal user account if you do not have system role 37 ("Client: Show system recycle bin"). If this system role is assigned to your account, you will get all recycle bin objects of all users. If you want to load the recycle bin objects of a particular user or your own, you can add a WHERE clause with your user name like in the following example. The placeholder <User Name> must be filled with the login name of the user of whom you want to load the recycle bin objects.

Please note that you need system role 37 to be able to load the recycle bin objects for any other user than yourself.

SELECT * from system:trash WHERE system:lastModifiedBy = '<User Name>'

3.15 Queries for Parent and Child Object Types

Since version 5.1.0 for enaio^® 10.0 Final and above, it is possible to query parent and/or child object types. It is possible to set conditions on parent and child fields. In this subchapter, we will describe this feature step by step, show how it works and which restrictions apply. The following example gives a first overview:

SELECT d.* FROM PatientDokument d, Patient f WHERE f.patientId = 3756 AND d.system:creationDate > "2021-01-01" ORDER BY d.system:creationDate DESC

The above example shows a query for the object types with the internal names PatientDokument and Patient. Based on the object type definition, the parser will automatically detect that the object type PatientDokument is a document object type while Patient is a folder object type. This results in a query for objects of the PatientDokument object type with a parent object condition on the folder object. Because enaio^® has a hierarchy architecture, it is not necessary to create joint conditions like in regular SQL (WHERE d.xyz = f.yxd). The connections between the different object types are set almost automatically and only in a few cases the parser needs more information about their relationship, which we will describe in the following.

The fixed connections between object types lead to some basic rules which must be adhered to when creating combined queries:

The first object type after the FROM keyword is the leading object type. It defines the starting level: document, register, or folder level.
- If the first object type is a document object type, all non-document object types are parent object types (see <ParentObjects> in the enaio^® server-api documentation).
- If the first object type is a folder object type, all other object types are child object types (see <ChildObjects> in the enaio^® server-api documentation).
- If the first object type is a register object type, document object types are child object types and folder object types are parent object types.
- If the first object type is a register object type and the query contains additional register object types in the FROM part, the parser needs more information on each additional register object type, whether it is a child or parent object type. This information is given in the shortcut of the object type which is separated with a space character from the internal name of the object type:
  - If the shortcut starts with a lower-case c, the register is a child object type: SELECT r.* FROM RegisterType1 r, RegisterType2 c WHERE r.system:objectId = 4711
  - If the shortcut starts with a lower-case p, the register is a parent object type: SELECT r.* FROM RegisterType1 r, RegisterType2 p WHERE r.system:objectId = 4711
  - If the shortcut starts with a different character, both register object types are standalone: SELECT * FROM RegisterType1 r1, RegisterType2 r2 WHERE r1.system:objectId = 4711 AND r2.system:objectId = 4712
All object types in the FROM part must have a shortcut in which the object type is referenced in the SELECT, WHERE, and ORDER part in the fields. The shortcut is added after the internal name of the object type with a space between the name and the shortcut.
Conditions linked by OR must be object type clean. It is not allowed to write OR conditions over two object types (with different shortcuts) like: WHERE f.firstName = 'Harry' OR d.title = 'Document Title'.

If the result fields are not explicitly defined and the query contains an ALL fields selection like SELECT * FROM objectType1 a, objectType2 b WHERE ..., the * operator in the SELECT part of the query applies to all object types in the FROM part:

SELECT * FROM PatientDokument d, Patient f WHERE f.patientId = 3756 AND d.system:creationDate > "2021-01-01" ORDER BY d.system:creationDate DESC

The shown query will return all index fields for PatientDokument and Patient. If the result should only contain index fields for PatientDokument, the object type shortcut must be placed in front of the *:

SELECT d.* FROM PatientDokument d, Patient f WHERE f.patientId = 3756 AND d.system:creationDate > "2021-01-01" ORDER BY d.system:creationDate DESC

The * operator only queries all index fields of the affected object types and only the common system fields. If it is necessary to request special system fields, these must be queried explicitly:

SELECT d.*, d.system:createdBy, d.system:creationDate FROM PatientDokument d, Patient f WHERE f.patientId = 3756 AND d.system:creationDate > "2021-01-01" ORDER BY d.system:creationDate DESC

To query child objects in the query, it is necessary to specify the depth to which the childobjects should be returned. For this the exportDepth in the options must be specified:

ExportDepth: 0 → No child objects are returned.
ExportDepth: 1 → The direct children under the queried object are returned
ExportDepth: 2 → The direct children and the children of the children are returned.

For exportDepth greater than 3, the logic continues according to exportDepth = 2.

Parent-Register clauses
Conditions on parent registers like SELECT * FROM PatientDokument d, RegisterAufenthalt r WHERE r.system:objectId = 4711 are only applied on the direct parent register of the object. It is not possible to set conditions on the parent register of the register in which a document object is located.

Register-Register clauses
If the leading object type is a register and there are further register object types in the query, then it is not possible to bring them into a parent-child-condition like the following not valid statement is trying:
SELECT r.* FROM register01 r, register02 p WHERE r.maskField = 'Test' AND p.system:objectId = 4711
The above statement can't be executed by enaio in form of an relationship between both register object types (the second should be the parent register due to the alias "p"). The DMS Microservice is executing the register object types seperatly and the result contains all register01 which have the maskField value "Test" and also the register02 with objectId 4711.

Register result fields
Result fields for a parent register are only applied to the direct parent register, similar to the condition restriction above. It is not possible to apply them to the parent register of the register in which a document object is located.

Parent table field conditions
For Table field conditions on parent objects the enaio server must be configured with the Registry-Setting "DMS Options" → "UseExistsOnTableConditions" with the value 1.
Otherwise the server can't execute the query and will fail with an SQL Error.

3.16 Queries for Multiple Object Types

A query with multiple object types as described in chapter 3.14 can also be used to query multiple object types on the same level. Same level meaning document – document, register – register. Similar to parent – child queries, the first object type in the FROM part is the leading object type. The following shows an example for querying multiple object types:

SELECT d1.*, d2.*, d3.* FROM PatientDokument d1, AufenthaltDokument d2, BehandlungsDokument d3, Patient f WHERE f.patientId = 3756 ORDER BY d1.system:creationDate DESC, d2.system:creationDate, d3.system:creationDate

The leading object type in this example is the object type PatientDokument. Apart from the leading object type, there are two additional document object types: AufenthaltDokument and BehandlungsDokument. All index fields should be returned for all document object types and all document object types should be sorted in descending order according to their creation date. For the Patient folder object type, only the object ID should be returned and we want only document objects which are located in a folder object with patientId = 3756. The parser will apply the folder condition to all document object types in the query.

If the same query is to be used with register object types, the shortcuts must not start with c or p according to chapter 3.14. Otherwise, the additional register object types are treated as children or parents and not as parallel register object types.

Currently, the properties skipCount and maxItems are applied to each parallel object type individually. If maxItems is 500 and the query contains two parallel object types, then there could be up to 1000 result objects. We are investigating this behavior and reserve the right to change it once this feature leaves the Beta phase.

3.17 Queries for Multiple Independent Object Types

In contrast to chapters 3.14 and 3.15, where the different object types belong closely together in a cabinet or as parent-child relation, it is also possible to query different object types that are completely independent from each other using the union syntax. The union syntax is available for DMS microservice version 5.3.0 and higher. The following example shows three independent queries which each have conditions on object IDs.

SELECT PatientenID, familyname, firstname, dateofbirth FROM Patient_DMS_API WHERE system:objectId IN (1,2,3,4,5) 
UNION 
SELECT feld1, feld2, feld3, datum1 FROM register37 WHERE system:objectId IN (@registerIds) 
UNION 
SELECT feld2, feld17, feld16, responsible FROM Dokument_DMS_API WHERE system:objectId IN (@documentIds)

Some microservice endpoints including the DMS microservice return objects from the entire system and often it is unknown beforehand which objects will be returned. Take, for example, the system-wide full-text search for objects in the inbox. Here, all kinds of object types can be returned. These endpoints/searches return only search-specific data such as the full-text score and the returned objects' objectId and objectTypeId. Based on this information, it is possible to query using a single union search call to request all required index data fields. For each object type, there is one SELECT statement with the index data fields to load and all SELECT statements are concatenated with a UNION keyword.

Using the UNION keyword not only objectId conditions are possible. You can use all the of queries we have explained so far on this page in the different chapters and also combined ones from chapters 3.14 and 3.15. The following example shows a query which also includes ordering.

SELECT feld1, feld2 FROM Register222 WHERE feld1 <> "Test"
UNION 
SELECT feld1, feld2, feld3 FROM register37 WHERE feld3 > 70 || feld3 < 10
UNION 
SELECT r.feld1, r.feld2 FROM register37 r WHERE r.feld2 = "Test"
ORDER BY feld1 DESC, r.feld2 ASC

This example shows three queries with different conditions. The ordering is defined by adding a single ORDER BY clause at the end. It is not possible to add ORDER BY clauses after each individual SELECT statement. Because of this restriction, ORDER BY fields are applied to all SELECT statements if the selected object type has a field with a matching internal name. If an ORDER BY field should only be applied to certain SELECT statements, or if a SELECT statement contains only one object type, then table prefixes must be used as described in chapter 3.14.

The UNION keyword is not supported in queries for the history/audit log, user tray and locked objects (chapter 3.11 – 3.13). These queries must be carried out separately.

3.18 Queries for Favourites

Since version 6.0.0 for enaio^® 11.0 Final it is possible to query the favourite objects within the following query:

SELECT * FROM system:favourite

This query support paging with skipCount and maxItems. No further conditions are supported at the moment and the query always return for each object the properties system:objectId, system:objectTypeId and system:baseType. Only the * operator for quering both fields is supported at the moment.

DMS service for enaio®

[POST] /search

1. `/api/dms/objects/search` Endpoint

2. Properties

3. Query Language Definition

3.1 Simple Select Queries

3.2 Queries with Aliases

3.3 Queries with Conditions

3.3.1 Queries with Table-Field Conditions

3.3.2 Queries with Multilingual Catalogue Conditions

3.4 Queries for Full-Text

3.4.1 Full-Text on the entire System

3.4.2 Full-Text on certain object types

3.4.3 Full-Text query with Parent-Objects condition

3.4.4 Full-Text query with Object Id condition

3.4.5 Full-Text query condition

3.4.6 Full-Text queries with facets

3.4.7 Full-Text queries on a Field

3.4.8 Full-Text Automplete Suggestions

3.5 Queries with Sorting

3.6 Queries with Aggregations

3.7 Queries with Date Functions

3.8 Queries on contentstream Fields

3.9 Queries with System Fields

3.10 Queries with Parameters

3.11 Queries for Object History/Audit Logs

3.12 Queries for User Trays

3.13 Queries for Locked (Checked-out) Documents

3.14 Queries for Deleted Objects in the Recycle Bin

3.15 Queries for Parent and Child Object Types

3.16 Queries for Multiple Object Types

3.17 Queries for Multiple Independent Object Types

3.18 Queries for Favourites

Related content

[POST] /search

1. /api/dms/objects/search Endpoint

2. Properties

3. Query Language Definition

3.1 Simple Select Queries

3.2 Queries with Aliases

3.3 Queries with Conditions

3.3.1 Queries with Table-Field Conditions

3.3.2 Queries with Multilingual Catalogue Conditions

3.4 Queries for Full-Text

3.4.1 Full-Text on the entire System

3.4.2 Full-Text on certain object types

3.4.3 Full-Text query with Parent-Objects condition

3.4.4 Full-Text query with Object Id condition

3.4.5 Full-Text query condition

3.4.6 Full-Text queries with facets

3.4.7 Full-Text queries on a Field

3.4.8 Full-Text Automplete Suggestions

3.5 Queries with Sorting

3.6 Queries with Aggregations

3.7 Queries with Date Functions

3.8 Queries on contentstream Fields

3.9 Queries with System Fields

3.10 Queries with Parameters

3.11 Queries for Object History/Audit Logs

3.12 Queries for User Trays

3.13 Queries for Locked (Checked-out) Documents

3.14 Queries for Deleted Objects in the Recycle Bin

3.15 Queries for Parent and Child Object Types

3.16 Queries for Multiple Object Types

3.17 Queries for Multiple Independent Object Types

3.18 Queries for Favourites

Related content

1. `/api/dms/objects/search` Endpoint