Queries and Indexing

Query Format & Operators

A DB8 query takes the following form of JSON object.

{
    "select"  : string array,
    "from"    : string,
    "where"   : [ {
        "prop"    : string,
        "op"      : string,
        "val"     : any
  } ]
    "orderBy" : string,
    "desc"    : boolean,
    "incDel"  : boolean,
    "limit"   : integer,
    "page"    : string
}

 

See Query object  in the Database API Reference Guide for field descriptions.

 

For example, let's assume we are given the following task. 

Give me the display name and state for all contacts living in California, order by display name, return in descending order, include records that have been deleted, return up to 40 records, and use the "next page" key returned from the previous query.

Then our query object would look like the following:

{
    "select"  : ["displayName", "state"],
    "from"    : "com.yourdomain.contacts:1",
    "where"   : [{"prop":"state","op":"=","val":"CA"}],
    "orderBy" : "displayName",
    "desc"    : true,
    "incDel"  : true,
    "limit"   : 40,
    "page"    : "BGhlbGxvAAs3"
}

 

Queries allow you to do the following: 

  • Specify the following filter operators.
    Operator Description
    = equals
    < less than
    > greater than
    >= greater than or equal
    != not equal
    % wildcard
    ? full-text search

    Though there is no specific range filter operator, you could get a range using other filter operators (i.e., '>' and '<') in two where clauses. 

    The "%" operator (aka - the prefix operator) is a type of wildcard -- it will return all matches beginning with the value you specify.

     

     

     

     

     

     

    For example:
    "where":[{"prop":"displayName","op":"%","val":"J"}]

    This where clause returns all objects whose displayName field begins with 'J'.

    In SQL, this JSON formatted where clause would look like this:

    WHERE displayName LIKE 'J%'

 

  • Order objects on a specified field in ascending or descending order.
  • Limit the number of objects returned (maximum is 500).
  • Get the number of objects that would have been returned if there had not been a limit.
  • Use a page identifier (key) returned from one query to get the next page of objects.

All Queries Must Be on Indexed Fields

It is important to note that all queries must be on indexed fields and the indexes property you create determine the kind of queries you can make. This means two things:

 

  1. You can only query on an indexed property
  2. Your queries are limited to those that can be answered with contiguous results from the index table(s).

You define the indexes property with IndexClause object when you create a kind with the putKind() call. You can create multiple indices for your kind, each of which can consist of multiple properties. Each entry in an index is an array of values. So for instance, with the example we have seen in DB8 Service Basics, if you create a multiple property index composed first of a state property and then a displayName property, the entries would look like this:  

["CA", "John Doe"], ["CA", "Mabel Syrup"], ["OR", "Don Juan"]...

 

The indices are ordered first by state, then displayName. This means you could get results if you queried on state (i.e., state = "CA"), since state values are contiguously ordered in the index, but not if you queried on displayName.

 

Not only do results have to be contiguously ordered, but they have to be guaranteed to be contiguously ordered in advance. What does this mean? Let's take an example; given the following [state, displayName] index:  

["CA", "Helen Wheels"], ["CA", "Jerry Seinfeld"], ["CA", "John Doe"],
["CA", "Mabel Syrup"], ["CA", "Tony Soprano"]

 

You could do a search on a single displayName entry, i.e., "where" :[{"prop":"displayName", "op":"=", "val":"Tony Soprano"}] that would return one result. It would meet the criteria of being contiguously ordered, but there is no way of guaranteeing that in advance. Property values are not guaranteed to be unique and there could be multiple "Tony Soprano" entries in different states. To remedy this, you could create an additional index consisting of displayName first and, optionally, other properties after that.

  

You need to keep the above information in mind when you are creating your indexes and how your data is going to be accessed.

 

Navigation