Query operators & data types¶

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

Operators¶

.. raw:: html

Operator

.. raw:: html

Purpose

.. raw:: html

Example

.. raw:: html

‘<‘ or ‘lt’

.. raw:: html

Less than

.. raw:: html

select * where quantity > ‘1000’

.. raw:: html

‘<=’ or ‘lte’

.. raw:: html

Less than or equal to

.. raw:: html

Example

.. raw:: html

‘=’ or ‘eq’

.. raw:: html

Equals

.. raw:: html

select * where price = ‘20.00’

.. raw:: html

‘>=’ or ‘gte’

.. raw:: html

Greater than or equal to

.. raw:: html

select * where quantity >= ‘1000’

.. raw:: html

‘>’ or ‘gt’

.. raw:: html

Greater than

.. raw:: html

select * where quantity > ‘1000’

.. raw:: html

not

.. raw:: html

Subtraction of results

.. raw:: html

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

.. raw:: html

and

.. raw:: html

Union of results

.. raw:: html

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

.. raw:: html

or

.. raw:: html

Intersection of results

.. raw:: html

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

.. raw:: html

contains

.. raw:: html

Narrow by contained text

.. raw:: html

select * where title contains ‘tale’

.. raw:: html

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

.. raw:: html

string

.. raw:: html

'value', unicode '\uFFFF', octal '\0707'

long

.. raw:: html

1357412326021

Timestamps are typically stored as long values.

.. raw:: html

float

.. raw:: html

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.

.. raw:: html

boolean

.. raw:: html

true | false

UUID

.. raw:: html

ee912c4b-5769-11e2-924d-02e81ac5a17b

Array

.. raw:: html

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

object

.. raw:: html

For a JSON object like this one:

.. raw:: html

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

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

.. raw:: html

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

.. raw:: html

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

.. raw:: html