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’

contains

Narrow by contained text

select * where title contains ‘tale’

and

Union of results

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

or

Intersection of results

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

Precedence

The operators at the bottom of the above table are the ones with lower precedence. When a query is evaluated the comparison operators (=, > , <, <= and >=) will be evaluated first. And next “not”, “contains” and “or” will be evaluated and in that order.

Though they are not shown above, parentheses are allowed and may be used to group query expressions.

For example, given our rules of precedence, these two queries are equivalent:

select * where age > 6 or size = 'large' and color = 'tabby'

select * where (age > 6 or size = 'large') and color = 'tabby'

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.