Query API

The Query API is a simple ad-hoc query mechanism that can retrieve entities in whole or in part and with values obtained from related entities. This API is read only and is accessed using GET requests.

URL

The Query API is accessed via an HTTP(S) GET to a URL off of the root of the virtual directory. The components of the URL are as follows:

~api/query/type?fields=Field1,Field2

&filter=foo

&sortOrder=foo

&search=foo

&searchField=foo

&start=0

&limit=25

The components that are replaced by real values are highlighted. The meaning of the various parameters are as follows:

• Type: entity type to retrieve (e.g., “section”).
• Fields: names of the fields to retrieve.
• Filter: filter expression to limit the returned entities. See Filter Expressions below for details.
• SortOrder: names of the fields by which to sort the returned entities. This is a comma-separated list of field names prefixed by “+” or “-“ to sort ascending or descending, respectively. For example, “+Name,-ModifiedDate”.
• SearchField: field on which to search (must match the primary sort field).
• Search: value to find in the result set. The position where this value should occur (based on sortOrder) is returned along with the page that contained that value.
• Start: zero-based index of the first result entity the return. This is used to perform pagination and does not combine with search/searchField. The default is to start with the first item (0).
• Limit: number of items to return in the result set. This limits both start and search results. The default is to return all results.

The result is encoded based on the option view parameter whose default is “JSON”4. The object returned looks like this: 

{

totalRecords: 427,

data: [

       [ “A”,2,”xyz” ], // requested fields of first entity

       ...

       // up to limit elements

]

}

The totalRecord property holds the total number of results, regardless of the “limit” parameter. The “data” property holds an array of arrays. Each array in the “data” array contains the field values based on the “fields” parameter.

Field Names

Normally the “fields” parameter contains simple field names such as “Name” or “Capacity”. The fields, however, can reference related entities by using the dot-syntax. For example, to get the ID of each Room along with the Name of its Building and Campus, you would do this:

~api/query/room?fields=Id,Building.Name,Building.Campus.Name

The “Building” component of the second field is the name found in the “join” object that describes a one-to-one association from Room to Building. This is similar for the third field except that “Campus” is an association from the Building entity. The dot-syntax can follow as many association references as necessary.

Filter Expressions

The ability to control the entities returned in a query comes from the filter parameter. The syntax of a filter is C-like with some SQL extensions. The following C relational operators are supported:

==   Equality

!=    Inequality

<     Less-than

<=   Less-than or equal

>     Greater-than

>=   Greater-than or equal

&&  Logical And

||    Logical Or

!      Logical Not

&    Bitwise AND

|     Bitwise OR

In addition, the following SQL-style operators are supported:

?=   Like  (e.g.  “Name ?= ‘Bob%th’”)

in    IN  (e.g.  “Capacity in (2,4,8)”)

Parentheses are also supported. If no filter expression is given, then all entities of the specified type are returned.