Send Docs Feedback

Querying your data

This article describes how to use queries to filter data retrieved from your backend data store. Queries allow you to work with only the data you need, making your app more efficient and manageable by reducing the number of entities returned or acted on by the API.

A query can be sent with any GET, PUT or DELETE request. For example, you might query the API to retrieve only the user entities with the property status:'active' to get a list of your users that have active accounts.

API BaaS is not an RDBMS. It does not include RDBMS features such as count(*) and cross-table joins. Thought it supports SQL-like syntax for simple queries to filter data, API BaaS will not perform well with complex query string such as ql=select * where color='red' and size='large' and shape='circle' and lineWeight='heavy' and lineColor='blue' and overlay='Circle' and other='things'.

For more about queries and performance, see Evaluating API BaaS as a data store and Data store best practices.

For information on more advanced query usage and syntax, see Query parameters & clauses.

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.

Request construction

Queries are sent to the Apigee API as part of the request URL, and are made up of two parts:

  • The path to the collection you want to query
  • The query language statement containing your query

These two statements are separated by ?ql= to indicate where the query language statement starts.

For example, a GET request to the following URL would return all the entities in the users that contain the property status:'active':

https://api.usergrid.com/your-org/your-app/users?ql=status='active'

SQL-like syntax

The Apigee API supports a SQL-like query syntax that will be familiar to most users that have experience working with relational databases, including operators such as and, or, and not. As with SQL queries, these operators can be used to form complex queries that return a more refined result set.

For example, the following would query the Apigee API for the first 5 user entities that contain the properties status:'active' and age less than 40, in ascending order by their name property:

https://api.usergrid.com/your-org/your-app/users?limit=5&ql=status='active' and age lt 40 order by name asc

For a complete list of supported operators and data types, see Supported query operators & data types. For more on advanced query usage see Query parameters & clauses.

Writing efficient queries

API BaaS enables the fast retrieval of one of a large number of entities very quickly (random I/O). It does not perform well for retrieving an entire collection in excess of 1,000 entities.

On the backend API BaaS stores your data in a NoSQL (Cassandra) database. This makes your data store extremely flexible, but also requires a different approach to storing and retrieving data than users of a traditional RDBMS will be accustomed to.

For more information, see our API BaaS DBMS overview and Data store best practices.

As a best practice, you should include no more than 3 parameters in your queries. The API will not prevent you from submitting a query with more than 3 parameters; however, due to the nature of NoSQL, queries with many parameters can quickly become very inefficient.

Basic query usage

The following examples show how to query the Apigee API to return the first 5 entities in the users collection that contain the property status:'active'.

Optimizing queries

As a best practice, you should include no more than 3 parameters in your queries. The API will not prevent you from submitting a query with more than 3 parameters; however, due to the nature of NoSQL, queries with many parameters can quickly become very inefficient.

For more information, see our API BaaS DBMS overview and Data store best practices.

Request Syntax

https://api.usergrid.com/<org>/<app>/<collection>?ql=<query_statement>

Note: Any values specified in the query statement should be enclosed in single-quotes.

https://api.usergrid.com/your-org/your-app/users?limit=5&ql=select * where status = 'active'

Alternatively, when you use a statement that starts select * where you can omit the first part of the statement and abbreviate it this way:

https://api.usergrid.com/your-org/your-app/users?limit=5&ql=status = 'active'
-(NSString*)queryEntities {

	//create an instance of AppDelegate
	//we recommend you call ApigeeClient from your AppDelegate.
	//for more information see the iOS SDK install guide:
	//http://apigee.com/docs/app-services/content/installing-apigee-sdk-ios	
	AppDelegate *appDelegate = (AppDelegate *)[ [UIApplication sharedApplication] delegate];

	//specify the entity collection to query
	NSString *endpoint = @"users";
	
	//specify a valid query string
    NSString *query = @"status='active' &limit=5";
    
	//call getEntities to initiate the API call
	ApigeeClientResponse *response = [appDelegate.dataClient getEntities:endpoint queryString:query];
	
	@try {
	    //success
	}
	
	@catch (NSException * e) {
	    //fail
	}

}
		
//Create client entity
String ORGNAME = "your-org";
String APPNAME = "your-app";        
ApigeeClient apigeeClient = new ApigeeClient(ORGNAME,APPNAME);
ApigeeDataClient dataClient = apigeeClient.getDataClient();

//specify the entity collection to query
String type = "users";

//specify a valid query string
String query = "status = 'active' &limit=5";

  
//call getEntitiesAsync to initiate the asynchronous API call    
dataClient.getEntitiesAsync(type, query, new ApiResponseCallback() {	
	
	//If getEntitiesAsync fails, catch the error
	@Override
	public void onException(Exception e) { 
		// Error
	}
	
	//If getEntitiesAsync is successful, handle the response object
	@Override
	public void onResponse(ApiResponse response) {
	    try { 
	        if (response != null) {
	            // Success
	        }
	    } catch (Exception e) { //The API request returned an error
	        	// Fail
	    }
	}
});	
		
var dataClient = new Apigee.Client({
    orgName:'your-org', //your Apigee organization name
    appName:'your-app'
});			
			
var options = {
	endpoint:"users", //the collection to query
	qs:{ql:"status='active'",limit:5"} //the query string - note the use of the 'ql' property
};

//Call request to initiate the API call
dataClient.request(options, function (error, response) {
	if (error){
		//error
	} else {
		//success
	}	
});								
		
#Create a client object
usergrid_api = 'https://api.usergrid.com'
organization = 'your-org'//your Apigee organization name
application = 'your-app'

client = Usergrid::Application.new "#{usergrid_api}/#{organization}/#{application}"

begin

# Specify the name or uuid of the collection to query, and a query statement
response = client['users'].query("status = 'active' &limit=5")

rescue
#fail
end				
		
var dataClient = new Usergrid.client({
    orgName:'your-org', //your Apigee organization name
    appName:'your-app'
});			
			
var options = {
	endpoint:"users", //the collection to query
	qs:{ql:"status = 'active' &limit=5"} //the query string - note the use of the 'ql' property
};

// Call request to initiate the API call
dataClient.request(options, function (err, data) {
    if (err) {
        //error - GET failed
    } else {
        //data will contain raw results from API call
        //success - GET worked
    }
});

The easiest way to try out queries you're considering is to use the admin portal, which you can reach at https://apigee.com/appservices/#!/.

To try out queries in the portal, use the following steps:

  1. Go to the Data Explorer using the left navigation pane.
  2. Under Method, select the HTTP method you want to use, as follows:
    • GET to retrieve data.
    • POST to create data.
    • PUT to update data.
    • DELETE to delete data.
  3. In the Path box, enter the path to the collection you're querying.
  4. In the Query String box, enter your query string.

    Note that you put the path and query string in separate fields, rather than appending the query string to the path in the Path box.

Querying sub-properties

You can also query the value of sub-properties by using dot-notation in your query.

For example, given this entity property:

{
	"item": {
		"quantity": 2,
		"name": "potato chips"
	}
}

You could query for the name like this:

/users?ql=item.name = 'potato chips'

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'

Response syntax

When you query your data, the API response is formatted in JavaScript Object Notation (JSON). This is a common format used for parameter and return values in REST web services.

Data corresponding to the response is captured in the response’s entities array. The array will include one JSON-object for each entity returned for the query. Each returned entity will include a number of default properties, including the UUID of the entity, the entity type, and values for properties such as name, username, email, and so on. For a complete list of default properties by entity type, see Default Data Entity Types.

For example, the following query for all entities of type user where the name property equals 'Gladys Kravitz':

/users?ql=select * where name = ‘Gladys Kravitz’

will return the following response:

	{
	  "action" : "get",
	  "application" : "8272c9b0-d86a-11e2-92e2-cdf1ce04c1c0",
	  "params" : {
	    "ql" : [ "select * where name = 'Gladys Kravitz'" ]
	  },
	  "path" : "/users",
	  "uri" : "http://api.usergrid.com/myorg/myapp/users",
	  "entities" : [ {
	    "uuid" : "d0d7d0ba-e97b-11e2-8cef-411c466c4f2c",
	    "type" : "user",
	    "name" : "Gladys Kravitz",
	    "created" : 1373472876859,
	    "modified" : 1373472876859,
	    "username" : "gladys",
	    "email" : "gladys@example.com",
	    "activated" : true,
	    "picture" : "http://www.gravatar.com/avatar/20c57d4f41cf51f2db44165eb058b3b2",
	    "metadata" : {
	      "path" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c",
	      "sets" : {
	        "rolenames" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/rolenames",
	        "permissions" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/permissions"
	      },
	      "connections" : {
	        "firstname" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/firstname",
	        "lastname" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/lastname"
	      },
	      "collections" : {
	        "activities" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/activities",
	        "users" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/users",
	        "feed" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/feed",
	        "groups" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/groups",
	        "roles" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/roles",
	        "following" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/following",
	        "followers" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/followers"
	      }
	    }
	  } ],
	  "timestamp" : 1374694196061,
	  "duration" : 48,
	  "organization" : "myorg",
	  "applicationName" : "myapp",
	  "count" : 1
	}

Compare the preceding example with the following for another kind of query. Imagine the following request string, where the query string is asking for only the values of two of the entity’s properties (username and name):

	/users?ql=select username,name where name=’Gladys Kravitz’

In the response JSON from this query, the return value is specified as the property of the list item -- here, an array containing only the values of the properties the query asked for, in the order they were requested (username first, then name).

	{
	  "action" : "get",
	  "application" : "8272c9b0-d86a-11e2-92e2-cdf1ce04c1c0",
	  "params" : {
	    "ql" : [ "select username,name where name='Gladys Kravitz'" ]
	  },
	  "path" : "/users",
	  "uri" : "http://api.usergrid.com/myorg/myapp/users",
	  "list" : [ [ "gladys", "Gladys Kravitz" ] ],
	  "timestamp" : 1374697463190,
	  "duration" : 25,
	  "organization" : "myorg",
	  "applicationName" : "myapp",
	  "count" : 1
	}

 

Help or comments?