Query operators & data types

The following operators and data types are supported by the SQL-like query language in Usergrid.

Operators

Operator

Purpose

Example

‘<’ or ‘lt’

Less than

select * where quantity > ‘1000’

‘<=’ or ‘lte’

Less than or equal to

Example

‘=’ or ‘eq’

Equals

select * where price = ‘20.00’

‘>=’ or ‘gte’

Greater than or equal to

select * where quantity >= ‘1000’

‘>’ or ‘gt’

Greater than

select * where quantity > ‘1000’

not

Subtraction of results

select * where quantity < ‘4000’ and not quantity = ‘2000’

and

Union of results

select * where quantity > ‘1000’ and quantity < ‘4000’

or

Intersection of results

select * where quantity = ‘1000’ or quantity = ‘4000’

contains

Narrow by contained text

select * where title contains ‘tale’

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

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

Array

 
["boat", "car", "bike"]

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.

Please note that object properties are not indexed. This means queries using dot-notation will be much slower than queries on indexed entity properties.