Queries and parameters

Query format

You can issue a query in an API request to retrieve items from a collection. Here is the typical format for queries:

/{collection}?ql={query}

where

{query} is a query in the query language.

The query language supports a SQL-like query format. For example, the following request retrieves users whose Facebook first name is John:

/users?ql=select * where facebook.first_name ='john'

The {query} value is simply the URL-encoded version of the following:

facebook.first_name='john'

Components for complex queries

You can build complex expressions when querying. This allows you to use multiple Boolean expressions and operands to define your query.

'value', unicode '\uFFFF', octal '\0707'

10, -10

10.1, -10.1, 10e10, 10e-10, 10E10, 10E-10

'<' or 'lt'

'<=' or 'lte'

'=' or 'eq'

'>=' or 'gte'

'>' or 'gt'

NOT [field] = [value]

name contains '[search string]'

name contains 'foo*'

name = 'foo*'

location.coordinates within .5 of 40.042016, -86.900749

and

or

not

order by name asc

order by name desc

order by birthdate desc, name asc

For example:

Select all activities with the word foo in the content:

/activities?ql=select * where content contains 'foo'

Select all items except those that have an a property value of 5:

/items?ql=select * where NOT a = 5

Query parameters

You can also pass the following parameters in the query:

For example:

Select all users whose name starts with fred, and return the results in reversed order:

/users?ql=select * where name = 'fred*'&reversed=true

Examples

Here are additional examples:

Select all users users whose Facebook first name is John and whose Facebook last name is Doe:

/users?filter=facebook.first_name='john'&facebook.last_name='doe'

Select all activities where the category is usermessage and content contains the word hello in the string:

/activities?ql=select * where category = 'usermessage' and content contains 'hello'

Select all activities where the category is usermessage and field called fieldname contains hello or goodbye listed in descending order by date created:

/activities?ql=select * where category = 'usermessage' and (fieldname contains 'hello' or content contains 'goodbye') order by created desc

Select all activities where the word bar is not included in the field called fieldname:

/activities?ql=select * where facebook.first_name ='john'select * where not fieldname contains 'bar'

Select all activities where the category is usermessage, but fieldname does not contain the word hello in the string:

/activities?ql=select * where category = 'usermessage' and not fieldname contains 'hello'

Select all items except those that have an a property value of 5:

/items?ql=select * where NOT a = 5

Select all restaurants that are with 1.0 meter of latitute 'x and longitude 'y' and whose name does not contain burger:

/restaurants?ql=select * where location within 1.0 of x, y AND NOT name contains 'burger'

Use numeric range bounds:

/tests?ql=select * where x >= 5 and x <= 10 and y >= 20 and y <=40

Select all users whose name starts with j. Return the entity whose UUID is ab225e6d-d503-11e1-b36a-12313b01d5c1 first:

/users?ql=select * where name = 'j*'&start=ab225e6d-d503-11e1-b36a-12313b01d5c1

Select all activities where the category is usermessage and content contains the word hello in the string. Limit the number of results to 5:

/activities?ql=select * where category = 'usermessage' and content contains 'hello'&limit=5

Retrieve all users. Use a cursor for paging:

/users?ql=select * &cursor=LTIxNDg0NDUxNDpnR2tBQVFFQWdITUFDWFJ2YlM1emJXbDBhQUNBZFFBUUQyMVZneExfRWVLRlV3TG9Hc1doZXdDQWRRQVFIYVdjb0JwREVlS1VCd0xvR3NWT0JRQQ 

Note: The admin portal transforms queries into standard URL-encoded parameters before issuing HTTP requests. For example:

/users?ql=select * where name = 'john*'

is transformed to:

/users?ql=select%20*%20where%20name%20%3D%20'john*'

Using matrix parameters

The logical place to put a query is in the query string, but what happens when you want to query a collection somewhere other than at the end of the path? The URI specification addresses this by allowing a form of embedded query strings inside the paths called matrix parameters.

In App services, this URL path:

/users/ed/friends?ql="location eq new york"/achievements?ql="level eq mayor"

is interpreted as this:

/users/ed/friends/location="new york"/achievements/level="mayor"

Querying complex objects

You can query complex objects like this one:

by using dot notation:

/mycollection/thing?ql="select * where items.name = 'rocks'"

Filters (deprecated)

Filters can be used as an alternative to query language queries. For example:

/{collection}?filter={query}

However, the use of filters is deprecated. The ql syntax is the preferred syntax for queries.