Saperion Object Query Language

The Saperion Object Query Language is a SQL-like query language that can be used to search or filter the contents of resource collections. It is designed to be used as a query parameter in a URL. All keywords are case-insensitive.

The general structure of a query is

SELECT <fields> WHERE <expression> ORDERBY <order>

The language does not contain a FROM clause, because the resources you search for are defined by the REST endpoint. The SELECT, WHERE, and ORDERBY clauses are all optional.

Identifiers

Identifiers are used to identify fields in the SELECT-, WHERE- and ORDERBY-clause. An identifier can use upper- and lower-case letters, numbers and the characters "_", "-", and ".". If an identifier collides with a reserved keyword like 'DATE', the identifier can be escaped by adding an exclamation mark in front of the identifier ('!DATE').

SELECT clause

The SELECT clause can be used to limit the properties returned for each item in the result list. As most resource collections return special list item-types with a fixed property set, this feature is only used for document, folder and lookup archive queries. The properties (or fields) to select are represented as a comma-separated list of identifiers. To select all fields, the wildcard * can be used.

SELECT field1, another-field, property_10

WHERE clause

The WHERE clause defines the search criteria. The syntax is very similar to that of SQL, except that only a subset of operators and expressions are supported and that the operators and expressions are designed to be URL-compatible. The following logical operators are supported:

Parentheses can be used to group expressions. An expression consists either of an identifier, a binary operator and a value, or of an identifier and a postfix operator. Multi-values are supported and consist of a comma-separated list of values surrounded by parentheses.

WHERE someField <binary operator> <value>
WHERE someField <binary operator> (<value1>, <value2>, <value3>)
WHERE someField <postfix operator>

The followind binary operators are supported:

EQ (equals)
GT (greater than)
LT (less than)
GE (greater or equal)
LE (less or equal)
LIKE (like SQL)
CONTAINS (to search for multi-values)
CONTAINSTEXT (to search for full text data)

The following postfix operators are supported:

IS EMPTY (to check if a field is empty)
IS NOT EMPTY (to check if a field is not empty)

WHERE someField IS EMPTY

The following data types are supported for values:

Integer: A number. Example: 123
Float: A signed floating-point number with "." as decimal separator. Example: -123.98
Long: A number with extended range. Required when searching for IDs in user management. Example: 123L
String: A quoted string. For LIKE searches, the wildcards * (some string) and & (some character) are supported. Example: "my string"
Date: A date consists of the keyword "DATE" and a quoted string in ISO-8601 extended local date format in parentheses. Example: DATE("2016-08-26")
Time: A time consists of the keyword "TIME" and a quoted string in ISO-8601 extended offset time format in parentheses. Example: TIME("10:15:30+01:00")
DateTime: A datetime consists of the keyword "DATETIME" and a quoted string in ISO-8601 extended offset date-time format in parentheses. Example: DATETIME("2016-08-26T16:05:30+01:00")

WHERE someString EQ "hello world" AND someInteger GT 7 AND MULTIVAL CONTAINS (1,2,3)

CONTAINSTEXT

The CONTAINSTEXT keyword can be used to execute full text queries. SOQL supports searching for simple terms, prefixes and logical combinations.

Simple term: "hello" (a quoted string)
Logical operators: AND, OR, NOT
Prefix terms: STARTSWITH "hello wor"

WHERE myFulltextField CONTAINSTEXT("hello world" OR STARTSWITH "hello")

ORDERBY clause

The ORDERBY clause defines the order of the returned query results. It consists of an identifier and an order. Multiple pairs of identifiers and orders can be specified as a comma-separated list. The following orders are supported:

ASC (ascending)
DESC (descending)

ORDERBY field1 ASC, field2 DESC

Paging

All resource collections use paging to limit the amount of data transferred from server to client. The paging is controlled by specifying the number of returned items and the offset to start from. The paging options can be specified as a URL parameter that contains the following two options:

elements: Defines the number of elements to return
offset: Defines the offset from the top of the result list

The paging parameter consists of these two options separated by ";". To return 100 elements starting from element 51, use the following parameter:

elements 100;offset 50

Usage

To add a query to a request, the query is added as a query parameter called 'query'. To add paging, the paging statement is added as a query parameter called 'paging'. The following are few examples.

Examples

1. Request used to search for users:

http://localhost:8080/api/management/users?query=WHERE displayName LIKE "User*" ORDERBY displayName ASC, fullName ASC&paging=elements 100;offset 0

2. Request used to search for deleted ACLs:

http://localhost:8080/api/management/deleted/acls?page=elements 100;offset 0&query=WHERE name LIKE "testACL*"

3. Request used to search for multivalue field value associated with a particular revision of a document:

http://localhost:8080/api/documents/revisions/ewoiaGRvYyIgOiAiNzg3QUM4QkY4Q0E0NDE0MjBGMDAwMzAwMDAwMDkyMDAwMDAwMDAwMDAwMDAiCn0=/index/multivalue_field?page=elements 100;offset 0&query=WHERE MULTIVAL CONTAINS (1,2,3)

4. Request used to search for a document in a given folder archive:

http://localhost:8080/api/folders/ewoiZGRjTmFtZSIgOiAiZm9sZGVyZGRjIiwKInN5c1Jvd0lkIiA6ICIkJCRST09UJCQkIgp9/documents?page=elements 100;offset 0&query=WHERE SYSROWID EQ "91D575B806263D48BF7E2603894EA8BD000000000000"

5. Request used to search for a folder in a given folder archive:

http://localhost:8080/api/folders/ewoiZGRjTmFtZSIgOiAiZm9sZGVyZGRjIiwKInN5c1Jvd0lkIiA6ICIkJCRST09UJCQkIgp9/folders?page=elements 100;offset 0&query=WHERE SYSFOLDERNAME LIKE "testFolder*"

6. Request used to search for the value multivalue field associated with a given folder archive

http://localhost:8080/api/folders/ewoiZGRjTmFtZSIgOiAiZm9sZGVyZGRjIiwKInN5c1Jvd0lkIiA6ICIkJCRST09UJCQkIgp9/index/multivalue_field?page=elements 100;offset 0&query=WHERE MULTIVAL LIKE "testValue*"

7. Request used to search for deleted groups

http://localhost:8080/api/management/deleted/groups?page=elements 100;offset 0&query=WHERE fullName LIKE "testGroup*"

8. Request used to search for deleted tenants

http://localhost:8080/api/management/deleted/tenants?page=elements 100;offset 0&query=WHERE name LIKE "testTenant*"

9. Request used to search for the lookup entries in the given archive

http://localhost:8080/api/archive/lookup-entries/ewogICJuYW1lIjogImxvb2t1cGRkYyIsCiAgImNvbnRlbnRUeXBlIjogIkxPT0tVUCIKfQ==/content?page=elements 100;offset 0&query=WHERE multivalue_field IS NOT EMPTY

10. Request used to search for the multivalue field for the given lookup entry

http://localhost:8080/api/lookup-entry/ewogICJkZGNOYW1lIjogImxvb2t1cGRkYyIsCiAgInN5c1Jvd0lkIjogIjQzRDc5ODk1NDM0QjRFOTNBNzZDOEQ2OUM2MkJFMDMxMDAwMDAwMDAwMDAwIgp9/index/multivalue_field?page=elements 100;offset 0&query=WHERE MULTIVAL LIKE "value%"