# Query Language

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

The following example retrieves a list of restaurants (from a
restaurants collection) whose name property contains the value "diner",
sorting the list in ascending order by name:

    /restaurants?ql=select * where name contains 'diner' order by name asc


## Basic syntax

Queries of Usergrid data for Apache Usergrid are made up of two kinds of
statements: the path to the collection you want to query, followed by
the query language statement containing your query. These two statements
are separated by "?ql=" to indicate where the query language statement
starts.

To retrieve items from a collection, you would use a syntax such as the
following:

    /<collection>?ql=<query_statement>

In the following example, the query is retrieving all users whose name
is Gladys Kravitz.

    /users?ql=select * where name = 'Gladys Kravitz'

The following example selects all items except those that have an a
property value of 5:

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

Note that there is a shortcut available when your query selects all
items matching certain criteria -- in other words, where you use a
statement that starts "select \* where". In this case, you can omit the
first part of the statement and abbreviate it this way:

    /items?ql=NOT a = 5

You query your Apache Usergrid data by using a query syntax that's like
Structured Query Language (SQL), the query language for relational
databases. Unlike a relational database, where you specify tables and
columns containing the data you want to query, in your Apache Usergrid
queries you specify collections and entities.

The syntax of Apache Usergrid queries only *resembles* SQL to
make queries familiar and easier to write. However, the language isn't
SQL. Only the syntax items documented here are supported.

## Supported operators

Comparisons

* Less than `<` or `lt`
* Less than or equal `<=` or `lte`
* Equal `=` or `eq`
* Greater than or equal `>=` or `gte`
* Greater than `>` or `gt`
* Not equal `NOT`

Logical operations

* Intersection of results `and`
* Union of results `or`
* Subtraction of results `not`


## Query Response Format

the query’s response is formatted in
JavaScript Object Notation (JSON). This is a common format used for
parameter and return values in REST web services.

So for the following query:

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

...you would get a response such as the the one below. The JSON format
arranges the data in name/value pairs. Many of the values correspond to
specifics of the request, including the request’s HTTP action (GET), the
application’s UUID, the request’s parameters (the query string you
sent), and so on.

Here, the query is asking for whole entities in the users collection.
Data corresponding to the response is captured in the response’s
`entities` array. The array has one member here, corresponding to the
one user found by the query (another kind of query might have found more
users). That one member gives the UUID of the entity (user), the entity
type, and values for properties such as name, username, email, and so
on.

```json
{
  "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",
        "devices" : "/users/d0d7d0ba-e97b-11e2-8cef-411c466c4f2c/devices",
        "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
    }


## Data types supported in queries

As you develop queries for your Apache Usergrid data, remember that entity
properties each conform to a particular data type (whether the entity is
included by default or an entity you defined). Your queries must
acknowledge this, testing with values that conform to each property's
data type. (You can view the list of property data types for the default
entities at [Default Data Entities](/default-data-entities).)

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 so that you can be
sure that query results are as you expect them to be.

So imagine you define an entity with a `price` property whose value
might be `100.00`. Querying for `100` will return no results even if
there are occurrences of `100.00` as `price` values in your data set.
That's because the database expected a decimal-delimited `float` value
in your query.


Data Type     Examples                                                                                    Notes
----------- ------------------------------------------------------------------------------------------- ---------
`string`    `'value'`, `unicode '\uFFFF'`, `octal '\0707'`                                              true | false
`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.

## 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 name contains 'diner'

**Note:** Not all string properties of the default entities are
indexed for searching. This includes the `User` entity's `username`
property.

This will return all users whose name property contains the word 'Kravitz'

    /users?ql=select * where name contains 'Kravitz'

This will return all users whose name property contains a word beginning with 'Krav'

    /users?ql=select * where name contains 'Krav*'

This will return all users whose name is exactly 'Gladys Kravitz'

    /users?ql=select * where name = 'Gladys Kravitz'


## 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:

    /users?ql=select * where lastname = 'Smith' order by firstname asc


    /users?ql=select * where lastname = 'Smith' order by firstname desc


    /users?ql=select * where lastname contains 'Sm*' order by lastname asc, firstname asc


## Geoqueries

Many of today's apps are enhanced by the use of *geolocation*, wireless
detection of the physical location of a remote device. These apps are
said to be *geolocation-aware* in that they query the device to
determine the user's position and then use this data to further enhance
the user's experience. For example, apps can capture the exact location
where a picture was taken or a message was created.

Usergrid support geolocation on any entity, both built in (e.g.,
users, groups) and user defined.

To add a location to any entity, include the following member to the
JSON in a POST or PUT call:

    "location": {
        "latitude": 37.779632,
        "longitude": -122.395131  
    } 

For example, to store a listing of restaurants and their locations,
start by creating a collection called restaurants:

    POST https://api.usergrid.com/org_name/app_name/restaurants

Next, add a new entity to the collection:

    POST https://api.usergrid.com/org_name/app_name/restaurants
    {
      "name": "Rockadero",
      "address": "21 Slate Street, Bedrock, CA",
      "location": {
        "latitude": 37.779632,
        "longitude": -122.395131
      }
    }

This creates a new restaurant entity called "Rockadero" with the
longitude and latitude included as part of the object.

When a location is added to an entity, it is easy to make queries
against that data. For example, to see all restaurants within a 10 mile
radius of the user's location, make a GET call against that entity, and
include a search query in the following format:

    location within <distance in meters> of <latitude>, <longitude>

If we use the location of our user Fred, we first need to convert miles
to meters. 1 mile is equivalent to 1609.344 meters, so 10 miles is about
16093 meters. Thus, the API call looks like this:

    GET https://api.usergrid.com/org_name/app_name/restaurants?ql=location within 16093 of 37.776753, -122.407846


## Managing large sets of results

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