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.

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. An identifier can use upper- and lower-case letters, numbers and the characters "_", "-", and ".". 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)

The following postfix operators are supported:

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
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 multiValue CONTAINS (1,2,3)

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)

ORDER BY 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