Common Query Examples

CMIS 1.0 CMIS 1.1

A collection of typical query use cases.

Simple Query

Find all documents in the repository that are named “hello world”.

SELECT * 
FROM   cmis:document
WHERE  cmis:name = 'hello world'

Complex query with JOINS

Join types POLICY with CLAIM on property POLICY_NUM and RISK with POLICY on cmis:objectId.

(Note: Not all repositories support JOIN)

SELECT Y.CLAIM_NUM, X.PROPERTY_ADDRESS, Y.DAMAGE_ESTIMATES, Z.BAND
FROM   (POLICY AS X JOIN CLAIMS AS Y ON X.POLICY_NUM = Y.POLICY_NUM)
       JOIN RISK AS Z ON X.cmis:objectId = Z.cmis:objectId
WHERE  (100000 = ANY Y.DAMAGE_ESTIMATES) AND Z.BAND > 3

Text search query

Find all documents that have at least one of the words “president”, “chancellor” or “directory” in the content. Depending on the repository and the repository configuration, also property values are included.

SELECT cmis:objectId, cmis:name, SCORE() AS score
FROM   cmis:document
WHERE  CONTAINS('president chancellor directory')

Query for multi-value property

Find all objects of type my:cars that have a radio or airbag as feature (my:features is a multi-value property).

SELECT cmis:objectId, cmis:name, my:features
FROM   my:cars
WHERE  ANY my:features IN ('radio', 'airbag')

Query for secondary types

CMIS 1.1

Find all documents that have a destruction date of 2015-12-31 (midnight UTC) in the secondary type cmis:rm_destructionRetention.

(Note: Repository does not have to support JOIN)

SELECT cmis:objectId, cmis:name
FROM   cmis:document JOIN cmis:rm_destructionRetention
       ON cmis:objectId = cmis:rm_destructionRetention:objectId
WHERE  cmis:rm_destructionDate = TIMESTAMP '2015-12-31T00:00:00.000Z'