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.
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.
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.
NULL, TRUE, and FALSE are available.
Integer literals may be expressed in a standard way
String literals must be included in single quotes
Timestamp literals must be used when comparing against timestamp fields. Timestamps are specified in ISO 8601 format in UTC time.
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.
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.
The following boolean operators are available for composing expressions
AND, OR, NOT, <, >, =, <>, <=, >=, IS NULL, IS NOT NULL, LIKE
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.
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'
- Support Documentation Portal
- IO Docs Definition
- Mashery API Documentation
- Mashery 3.0 API Guide
- Resource Hierarchy
- Pagination, Sorting, Filtering
- Example Use Cases
- Email Template Sets
- Mashery 2.0 API Guide
- OAuth Supporting Methods
- API Sandbox
- JSON RPC
- API Objects
- Fetching Objects
- Query Language
- Creating Objects
- Updating Objects
- Validating Fields
- Deleting Objects
- General Object Methods
- Plan Services
- Plan Endpoints
- Plan Methods
- Plan Filters
- Package Keys
- Developer Classes
- Defining Methods
- Service Definitions
- Service Definition Endpoints
- Service Definition Methods
- Service Definition Response
- Mashery 2.0 Reporting API Guide
- Event Trigger API
- Mashery 1.0 API Guide
- Mashery 3.0 API Guide
- Tips and Tricks
- Customizing Your Portal
- API Traffic Manager
- I/O Docs WSDL requirements