Was this helpful?

When querying your data, you can use your query string to get the data, then sort and manage it on the client. This topic describes a few techniques.

Note: For introductory information about queries, be sure to see App Services data query overview.
Note: Query examples in this content are shown unencoded to make them easier to read. Keep in mind that you might need to encode query strings if you're sending them as part of URLs, such as when you're executing them with the cURL tool.

Retrieving values for multiple properties

Your query can return multiple kinds of values -- such as the values of multiple properties -- by specifying the property names in your select statement as a comma-separated list.

For example, the following request returns the address and phone number of users whose name is Gladys Kravitz:

/users?ql=select address,phone_number where name = 'Gladys Kravitz'

Querying for the contents of text

Your query can search the text of entity values of the string data type. For example, you can search a postal code field for values that start with a specific three numbers.

For example, the following query selects all restaurants with the word diner in the name:

/restaurants?ql=select * where atmosphere contains 'fine'
Note: Not all string properties of the default entities are indexed for searching. This includes the User entity's username property.

The following table lists a few examples of the kind of searches you can do in queries.

Goal Example Notes
Find books whose 'title' property contains the full word "tale".
/books?ql=select * where title contains 'tale'
containslooks for the occurrence of a full word anywhere in the searched property.
Find books whose 'title' property contains a word that starts with "ta".
/books?ql=select * where title contains 'ta*'
containswill look for partial words if you include a wildcard.
Find books whose title property is exactly and only "A Tale of Two Cities".
/books?ql=select * where title = 'A Tale of Two Cities'
The = operator is looking for a match in the entire searched property value. Use a * wildcard to look for some set of the first characters only.

Finding a location

If you've stored location data with your entities, you can query for the proximity of the geographical locations those entities represent. For more information on geolocation, see Geolocation.

Goal Example Notes
Find stores whose locations are within the specified longitude and latitude.
/stores?ql=location within .5 of 40.042016, -86.900749

withinwill test for values within the value you specify. The within value is expressed as a number of meters.

The return results are sorted in order of nearest to furthest. If there are multiple entries at the same location, they're returned in the order they were added to the database.

For more on geolocation queries, see Geolocation.

Sorting results

You can return query results that are sorted in the order you specify. Use the order by clause to specify the property to sort by, along with the order in which results should be sorted. The syntax for the clause is as follows:

order by <property_name> asc | desc

The following table includes a few examples:

Goal Example
Sort by first name in ascending order
/users?ql=select * where lastname = 'Smith' order by firstname asc
Sort by first name in descending order
/users?ql=select * where lastname = 'Smith' order by firstname desc
Sort by last name, then first name in ascending order
/users?ql=select * where lastname contains 'Sm*' order by lastname asc, firstname asc

Managing large result sets

When your query might return more results than you want to display to the user at once, you can use the limit parameter with cursors or API methods to manage the display of results. By default, query results are limited to 10 at a time. You can adjust this by setting the limit parameter to a value you prefer.

For example, you might execute a query that could potentially return hundreds of results, but you want to display 20 of those at a time to users. To do this, your code sets the limit parameter to 20 when querying for data, then provides a way for the user to request more of the results when they're ready.

You would use the following parameters in your query:

Parameter Type Description
limit integer

Number of results to return. The maximum number of results is 1,000. Specifying a limit greater than 1,000 will result in a limit of 1,000.

Limit is applied to the collection, not the query string. For example, the following query will find the first 100 entities in the books collection, then from that set return the ones with author='Hemingway':

/books?ql=author = 'Hemingway'&limit=100

You can also use the limit parameter on a request without a query string. The following example is shorthand for selecting all books and limiting by 100 at a time:

/books?limit=100

Using a limit on a DELETE can help you manage the amount of time it takes to delete data. For example you can delete all of the books, 1000 at a time, with the following:

DELETE /books?limit=1000

Keep in mind that DELETE operations can take longer to execute. Yet even though the DELETE query call might time out (such as with a very large limit), the operation will continue on the server even if the client stops waiting for the result.

cursor string An encoded representation of the query position pointing to a set of results. To retrieve the next set of results, pass the cursor with your next call for most results.

For example:

Select all users whose name starts with fred, and returns the first 50 results:

/users?ql=select * where name = 'fred*'&limit=50

Retrieve the next batch of users whose name is "fred", passing the cursor received from the last request to specify where the next set of results should begin:

/users?ql=select * where name = 'fred*'&limit=50&cursor=LTIxNDg0NDUxNDpnR2tBQVFFQWdITUFDWFJ2YlM1emJXbDBhQUNBZFFBUUQyMVZneExfRWVLRlV3TG9Hc1doZXdDQWRRQVFIYVdjb0JwREVlS1VCd0xvR3NWT0JRQQ

Attaching a query to all API calls

JavaScript SDK only

The following feature is currently only supported by the Apigee JavaScript SDK

In some cases, it may be convenient to attach a query or other URI parameter to every call you make to API BaaS, such as a custom identifier or token. To do this with the Apigee JavaScript SDK, add a qs property to your Apigee.Client object when you initialize the SDK. For more on initializing the SDK, see our install guide.

For example, the following would append ?custom_id=1234 to every call sent from the Apigee JavaScript SDK to API BaaS:

var options = {
	orgName:'yourOrg',
	appName:'yourApp',
	qs:'custom_id=1234'
}
var dataClient = new Apigee.Client(options);

Complex queries (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 your backend data store, 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"

Comments

Your documentation is inconsistent. On the http://apigee.com/docs/app-services/content/querying-your-data page it says that "The parameter should be enclosed in double-quotes, and any values specified in the statement should be enclosed in single-quotes." None of these examples demonstrate that.

Thanks for the catch! Should be fixed now.

That's great... except that it doesn't work. I'm trying to make queries against the /devices collection. If I use the quotes, the request fails. But if I leave them off it works.

These fail:
/devices?client_id=****&client_secret=****&ql="select%20*%20where%20devicePlatform%20contains%20'android'"
/devices?client_id=****&client_secret=****&ql=%22select%20*%20where%20devicePlatform%20contains%20'android'%22

This works:
/devices?client_id=****&client_secret=****&ql=select%20*%20where%20devicePlatform%20contains%20'android'

Is there any way to retrive a random record from a collection?

Hi Valerio. No built-in way, no. But one of the developers suggests that since everything has a created date, you might do a Math.random()* some value. This will give you a created date between when your application started and the current time. You can then do this.

ql= select * where created < ${date} & limit = 1

Is there a list of operators? I see = and < in your examples. I tired != and NOT and they didn't work. Is there more than = and <?

Hey Shelly -- Check out the list in the "Supported Operators" section of this topic: http://apigee.com/docs/app-services/content/querying-your-data

Hello, I am getting an error and I have tried a series of different iterations. What I want is the limit to = 100; and I would like to order the list by the given level. Unofrtunately I get an error.
Can you please help me with this query line of code?

Failed attempts:
/levels?ql="order by level asc &limit=100"

Hi Daniel

The issue is the quotes around the string. This is an error in the docs. Using this should work: /levels?ql=order by level asc &limit=100

Any way to retrive the Count of a collection? I mean, for example, I like to know how many friends "Ed" has... something like: GET /users/ed/friends?ql=select count(*)

Sorry for this really late reply, Roberto. There is nothing like count in the current query language. The best you could do is set a very high limit and then count the entities that come back in the response.

Using 'order by' to retrieve a set of entities only returns those entities that have the property we're ordering on. I would expect the returned set to contain all entities with the sorted entities first and the remainder in their natural order (or sorted by a second level ordering.) Is this a bug or by design? Is there are a way around it via the query or do I need to get all entities and sort client side?

You would need to query to get all entities then sort client side. The query language has been designed with sql-like syntax, but since we are running on Cassandra behavior can sometimes differ from a standard sql call.

Add new comment

Provide your email address if you wish to be contacted offline about your comment.
We will not display your email address as part of your comment.

We'd love your feedback and perspective! Please be as specific as possible.
Type the characters you see in this picture. (verify using audio)

Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.