Query Language

The Mashery API provides a query language which can be used to generate ad-hoc queries for objects.  The query language is not designed to retreive any arbitrary data.  it is not intended to match the power of SQL.  The query language is designed to retrieve a set of objects of a specific type based on a few criteria, possibly including related objects.  It is designed to return a serialization in JSON of an internal object graph, not tables as one might find in a SQL query.

See object.query for details on the mechanics of making a query and processing the result set.

Choosing the Object Type To Return

Every object query must follow a minimal structure.  It must begin with the keyword SELECT. The query must specify a base object type for the query to return using the FROM keyword followed by a pluralized object type.  The query must also specify which fields from the base object will be returned.  The symbol * indicates all fields.  The field list must appear between the SELECT and FROM keywords.

SELECT * FROM members

This query indicates to return all fields of all member objects.

Keywords are case insensitive, but will be expressed in all upper case in this documentation.

The key used to make the object.query api call must have permission to read the base object type specified.  See the documentation for the fetch method associated with the base object type.

Selecting Object Fields To Return

A field list is a specification of which fields to return from the query.  The fields returned are a heirarchical serialization of an object graph beginning with the base object type.

Simple Fields

A field list can be a simple comma separated list of field names.

SELECT username, display_name FROM members

This query selects only the username and display_name fields from the members objects that it returns.

Selecting All FIelds

The * symbol can be used to specify all simple fields from an object.  This will not include any fields that represent related objects.

SELECT * FROM members

Selecting Related Objects

Fields that represent relationships can also be specified in the field list.

SELECT *, keys FROM members

This query selects all fields from members as well as including a keys field. That field will contain an array of keys objects related to each member object returned.  Note that when a field representing a relationship is returned, all fields from that related object are returned unless otherwise specified.  See the documentation for each object type for information on object relationship fields available.

Selecting Related Fields

The . symbol can be used to specify related fields in a field list. 

SELECT username, roles.name FROM members

This query returns a set of member objects, where each object has two fields.  The first field is the username and the second field, roles consists of an array of role objects related to that member with only the role name field returned.

Not that the all fields indicator (*) can be used in conjuction with the dereferencing symbol (.).  If a field representing a relationship is the last field in dereferecing expression, all fields are automatically returned.  Thus the following two queries are equivelent.

SELECT *, keys FROM members

SELECT *, keys.* FROM members

Here is a more complicated example.

SELECT username, keys.application.name, keys.apikey FROM members

Note that specifying keys.apikey and keys.application.name does not include any other fields from the keys or applications related to that member.  Also note that in the case of application you are returning the single application "application" vs "applications" related to that key.

Restricting the Objects Returned

The query may contain a WHERE clause which can restrict the records returned.  The where clause evaulates a boolean expression and only returns objects for which that expression is true.

The following elements are available for constructing expressions in the query language.

Constants

NULL, TRUE, and FALSE are available.

Integer Literals

Integer literals may be expressed in a standard way

123

String Literals

String literals must be included in single quotes

'example'

Timestamp Literals

Timestamp literals must be used when comparing against timestamp fields.  Timestamps are specified in ISO 8601 format in UTC time.

timestamp'YYYY-MM-DDThh:mm:ssZ'

select * from package_keys where created > timestamp'2016-01-13T10:00:00Z'

NOTE: When including date fields in queries, it is important to note that if 'timestamp' is not included, then the date data will actually be evaulated as a normal string and unexpected results can occur.

Fields

Field names may be used that belongs to the object named in the FROM clause.  Only fields with the queryable attribute may be used in the WHERE clause of a query.  See JSON Schema Extensions.

SELECT * FROM members WHERE username = 'Jeff'

Referencing relationship fields is not currently allowed in a where clause.

Boolean Operators

The following boolean operators are available for composing expressions

AND, OR, NOT, <, >, =, <>, <=, >=, IS NULL, IS NOT NULL, LIKE

Operator Precidence

Operatators use expected precedence.  Parenthesis may be used in expressions.

Operators and NULL values

if either operand to a operator is null, the result of the operation will be null.  If the WHERE expresion evaluates to null, no records will be returned.  Use IS NULL and IS NOT NULL to correctly test for null values.

Type Conversions

If mixed types are used in a boolean operation, a type conversion will be performed before comparison. This can lead to unexpected resuls in certain edge cases.

Internally, the TRUE and FALSE constants are represented by the integers 1 and 0, respectively.

If any operand is an integer, then the other operand will also be converted into an integer.  For example, this query will return all roles because the constant 'x' is converted to the integer 0 which does equal 0.

SELECT * FROM roles WHERE 'x' = 0

The string constant '0' will be converted to the integer 0 and the string constant '1' will be converted to the integer 1.

It is best to avoid mixing types in expressions.

Sorting the results

ORDER BY is supported for sorting the result set.  Only fields from the object specified in the FROM clause can be used.  Additionally, only fields with the sortable attribute may be used.  See JSON Schema Extensions.

An ASC or DESC modifier may appear after each field in the ORDER BY list.  If no modifer appears, ASC is assumed.

SELECT * FROM members ORDER BY created DESC

Paginating the results

Query results are always paginated.  There is a limit of fetching 100 records at one time.  By default, only the first 100 records are returned.

the ITEMS clause can specify an alternative number of records to return per page.

SELECT * FROM members ITEMS 25

By default the first page is returned.  The PAGE clause allows additional pages to be returned.

SELECT * FROM members PAGE 5

The two clauses are normally combined for a full paging solution

SELECT * FROM members PAGE 5 ITEMS 25

Restricting results based on relationships

There is a limited capability to restrict the results returned based on the relationship with other objects. The REQUIRE RELATED clause will restrict objects returned to only those objects which have a related object matching the specification.

REQUIRE RELATED is followed by the relationship name then the keyword WITH and finally a boolean expression restricting objects in that relationship.  Only fields from the relationship specified may be referenced in the WITH clause.  Fields from the object specified in the FROM clause may not be used.  Arbitrary joining is not supported.

An example query returning members who have been granted the administrator role:

SELECT * FROM members REQUIRE RELATED roles WITH name = 'Administrator'