Was this helpful?

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 to specify a subset of data to be retrieved, updated or deleted. 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.

For information on more advanced query usage and syntax, see Working with queries.

Introduction

Basic query 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

See below for a complete list of supported operators. For more on advanced query usage see Working with queries.

The syntax for querying the Apigee API resembles SQL to make queries familiar and easier to write; however, the language isn't SQL and not all SQL syntax is supported. Please note that only the syntax items documented here are supported.

For more information on supported syntax, see Supported operators below, and Working with queries.

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'.

URL encoding queries

The following examples are shown unencoded to make them easier to read. Query strings should be URL-encoded when sent using cURL.

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);
DataClient 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 (error, response) {
	if (error){
		//error
	} else {
		//success
	}	
});				
		

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.

Supported operators

The following table lists more of the operators that you can use in queries. Others are described later in this section.

Category Component Name Example
Equality operations Less than
'<' or 'lt'
Less than equal
'<=' or 'lte'
Equal
'=' or 'eq'
Greater than equal
'>=' or 'gte'
Greater than
'>' or 'gt'
Not equal
NOT <some_expression>
Logical operations Intersection of results
and
Union of results
or
Subtraction of results
not

Supported data types

As you develop queries, remember that entity properties each conform to a particular data type. For example, in the default entity User, the name property is stored as a string, the created date as a long, and metadata is stored as a JSON object. Your queries must be data type-aware to ensure that query results are as you expect them to be.

For example, if you create an entity with a price property with a value of 100.00, querying for 100 will return no results, since the API expected a decimal-delimited float value in your query.

For a list of property data types for each default entities, see Default Data Entities.

Data Type Examples Notes
string 'value', unicode '\uFFFF', octal '\0707'  
long
1357412326021
Timestamps are typically stored as long values.
float
10.1, -10.1, 10e10, 10e-10, 10E10, 10E-10
Your query must be specific about the value you're looking for, down to the value (if any) after the decimal point.
boolean
true | false
 
UUID
ee912c4b-5769-11e2-924d-02e81ac5a17b
UUID types are typically used for the unique IDs of entities. The value must conform to the following format (do not enclose with quotation marks):
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
.
object

For a JSON object like this one:

{
 "items": [
  {
   "name": "rocks"
  },
  {
   "name": "boats"
  }
 ]
}

... you can use dot notation to reach property values in the object:

/mycollection/thing?ql="select * where items.name = 'rocks'"
Objects are often used to contain entity metadata, such as the activities associated with a user, the users associated with a role, and so on.

Query 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 Entities.

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.

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
	}

 

コメント

What is the escape character?

For example if you need to query a name with an apostrophe ( select * where name = 'jimbo's' ). \ didn't work, and neither did using " instead of ' to wrap the string.

I also tried %27 to encode the ', but it didn't recognize it (note: %20 works for spaces though).

Hi Joe

Which SDK are you using? Can you post a snippet of the code you are trying to run? Thanks!

javascript SDK. Nothing I tried worked, even in admin portal. I ended up having to encode apostrophes as '>', and then decode back into apostrophes in app

javascript SDK. Nothing I tried worked, even in admin portal. I ended up having to encode apostrophes as '>', and then decode back into apostrophes in app

The example you give above for the javascript sdk for limit doesn't work: qs:{ql:"status = 'active' limit = 5"}
I'm doing qs:{ql:"name > ' ' limit = 50"}, and it gives me the default 10. Same for other properties I've tried.

Hi Joe

This was an error in the docs. qs:{ql:"status = 'active'", limit:5} should work

On the limit parameter:
The example, as pointed before does seem to be incorrect.
The limit parameter works only if passed as a http request parameter as in:

domain.com?limit=5&ql=select * where property='value'

Thanks Rodolfo. The curl examples should be fixed now.

Hello,

Can I get the device number connected to a user by a query?

Thank you

Hey Romain,

When you say "device number", which number do you mean?

Remember that a user can have more than one device. You could try /users/<user_uuid>/devices to retrieve the list of the devices.

Steve

Hi, please the limit param for iOS framework doesn't seem to work. As shown on the first example provided on this page.

Hey Kristoffer,

Does it work when you try using the following as the query string?

NSString *query = @"limit=5&status='active'"

Steve

コメントを追加

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.