Querying KiiObjects

You can run a conditional query for KiiObjects in a bucket. For example: a query that gets up to 10 KiiObjects that have a value of more than 10 for a key named "count" in descending order.

See Query Example for a query example.

To query KiiObjects, send a QueryRequest object to Kii Cloud as shown in the following example:

curl -v -X POST \
  -H "Authorization: Bearer {ACCESS_TOKEN}" \
  -H "Content-Type: application/vnd.kii.QueryRequest+json" \
  "https://api.kii.com/api/apps/{APP_ID}/users/me/buckets/{BUCKET_ID}/query" \
  -d '{
        "bucketQuery": {
          "clause": {
            "type": "eq", "field": "name",
            "value": "John Doe"
          },
          "orderBy": "name", "descending": false
        },
        "bestEffortLimit": 10
      }'

If you cannot get the expected result, check if you are querying the bucket in the correct scope (See here for more discussion).

The performance of query and sort operations can deteriorate if there are numerous KiiObjects. See Performance for more information.

QueryRequest object

Here is an example of a QueryRequest object:

{
  "bucketQuery":{
    "clause":{"type": "eq", "field": "name", "value": "John Doe"},
    "orderBy":"name",
    "descending": false
  },
  "paginationKey":"asd12ijdfasdfjadfjgk",
  "bestEffortLimit":10
}
  • The bucketQuery field defines a query condition. This is composed of the following:
    • The clause field specifies one or more query clauses. For more information, see Query clause.
    • The orderBy field and the descending field define how query results should be sorted. Query results are sorted by the field specified in the orderBy field. The sort order is descending if the descending field is set to true, and ascending if the descending field is set to false. By default, the sort order is descending. If the orderBy field is not specified, the sort order is unknown.
  • The paginationKey field is specified to get the next query results when the previous query response contained only a subset of all the query results that met the query condition. For more information, see Pagination below.
  • The bestEffortLimit field defines the maximum number of KiiObjects to be returned in a query response. If this field is not specified, the default value 200 is applied. The maximum accepted value is also 200. If you need to get more than 200 results, use pagination to divide query results into multiple query responses. For more information, see Pagination below.

Queryable fields must have the field name up to 250 characters and a value (String) up to 190 characters. Key-value pairs that exceed these limits will not be returned by any query using the field.

When the orderBy field is specified, query results are sorted by an integer field in numerical order and by a string field in dictionary order.

Query and sort do not work properly if the type of the field values is not consistent among KiiObjects.

Strings are queried in a case-sensitive manner. For example, if you query with a string of "alice", "Alice" stored in a KiiObject will not be returned.

Query and sort might not work as expected for real number fields. Real numbers whose fractional part is zero are treated as integers. Therefore, for example, 1.0 will be indexed as an integer 1 while 1.1 will be indexed as a real number 1.1. You cannot get the expected result because the difference between the different types of values will not be correctly evaluated. If you need to query or sort KiiObjects with a real number field, convert real numbers to integers by multiplying them with 10, 100, and so forth. Then save the converted integers in an integer field to use when you run a query.

Only the first-level fields within the JSON expression can be queried. You cannot get fields at the subsequent levels because fields at those levels are not indexed.

Query clause

A query clause is defined using one or more of the following clauses:

  • EqualClause: matches when the field value is equal to the specified value.

    { "type":"eq",  "field":"name",  "value":"John Doe" }
    
  • PrefixClause: matches when the field value starts with the specified string. This clause works only for strings.

    { "type":"prefix",  "field":"name",  "prefix":"John" }
    
  • RangeClause: matches when the field value is within the specified range.

    {
      "type": "range",
      "field" : "age",
      "upperLimit":10,
      "upperIncluded":true,
      "lowerLimit":3,
      "lowerIncluded":false
    }
    

    The following examples show how to define a range:

    • age >= 20 : {"type":"range", "field":"age", "lowerLimit": 20}
    • age > 20 : {"type":"range", "field":"age", "lowerLimit": 20, "lowerIncluded": false}
    • 20 <= age < 30 : {"type":"range", "field":"age", "lowerLimit": 20, "upperLimit":30, "upperIncluded":false}
  • AllClause: matches all KiiObjects in the bucket.

    { "type": "all" }
    
  • InClause: matches when the field value is equal to one of the specified values. Up to 200 values can be specified.

    {
      "type": "in",
      "field": "lastName",
      "values": [ "Garcia", "Smith", "Lopez", "Simpson" ]
    }
    

    When you use InClause, make sure that the type of the parameter values matches with the type of the JSON field. For example, a JSON string {"id" : 123} will not match with a string "123" but a number 123. If you specify multiple values to compare, the type must be the same among the values. Do not mix numbers and strings nor decimals and integers.

  • HasFieldClause: matches when the field has the specified field type. The supported types are STRING, INTEGER, DECIMAL, and BOOLEAN.

    {
      "type": "hasField",
      "field": "name",
      "fieldType": "STRING"
    }
    
  • AndClause: concatenates clauses with an "AND" operator.

    {
      "type": "and",
      "clauses": [
        {"type": "prefix", "field": "name", "prefix": "John"},
        {"type": "eq", "field": "age", "value": 30}
      ]
    }
    
  • OrClause: concatenates clauses with an "OR" operator.

    {
      "type": "or",
      "clauses": [
        {"type": "eq", "field": "name", "value": "John"},
        {"type": "eq", "field": "age", "value": 30}
      ]
    }
    
  • NotClause: nagates a clause with an "NOT" operator.

    {
      "type": "not",
      "clause": {"type": "eq", "field": "name", "value": "John"}
    }
    

    Querying with NotClause can decrease performance but you might be able to transform the clause to avoid a not operator. See Transforming a not clause for more information.

  • GeoBoxClause: matches when the field value (GeoPoint) is inside the specified GeoBox (rectangular area).

    A GeoBox is defined by two points, namely ne (northeast) and sw (southwest) points. See the following example.

    {
      "type": "geobox",
      "field": "mylocation",
      "box": {
        "ne": {
          "_type": "point",
          "lat": 11.0,
          "lon": 1.0
        },
          "sw": {
          "_type": "point",
          "lat": 1.0,
          "lon": 17.0
        }
      }
    }
    
  • GeoDistanceClause: matches when the field value (GeoPoint) is inside the specified GeoDistance (circular area).

    A GeoDistance is defined by a center point and a radius (in meters), like the following example.

    {
      "type": "geodistance",
      "field": "mylocation",
      "center": {
          "_type": "point",
          "lat": 11.0,
          "lon": 1.0
      }
      "radius": 1023,
      "putDistanceInto": "distance_to_mylocation"
    }
    

    The putDistanceInto field specifies the name of the field that will store the distance between the center point and a queried KiiObject. For example, a query that uses the GeoDistanceClause above will return a response like this:

    ...
    {
      "mylocation": {
        "_type": "point",
        "lat": 11.0,
        "lon": 1.0
      },
      "_id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
      "_modified": 1366965367393,
      "_created": 1366965367393,
      "_owner": "SYS_ADMIN",
      "_version": "1",
      "_calculated": {
        "distance_to_mylocation": 15605.156575746192
      }
    }
    ...
    

    The distance_to_mylocation field is stored inside the _calculated field. The number represents the distance between the center point and a queried KiiObject (in meters). This field can be used to sort query results but in ascending order only.

    If the putDistanceInto field is omitted when requesting a query, the _calculated field will not appear in the response.

Using predefined keys for query

In addition to key-value pairs created by your mobile app, you can use predefined keys for query.

Predefined keys include various fields such as the KiiObject ID and created time. For the list of predefined keys, see Predefined keys.

Pagination

The bestEffortLimit field of the QueryRequest object determines the maximum number of KiiObjects in a query response. If the number of query results is larger than the limit, Kii Cloud returns KiiObjects only up to the limit. Also, Kii Cloud may not return all KiiObjects when there are many query results, even if the number of query results is smaller than the number specified in the bestEffortLimit field.

In such a situation, execute a query with a pagination key.

You will execute the first query without the paginationKey field.

curl -v -X POST \
  -H "Authorization: Bearer {ACCESS_TOKEN}" \
  -H "Content-Type: application/vnd.kii.QueryRequest+json" \
  "https://api.kii.com/api/apps/{APP_ID}/users/me/buckets/{BUCKET_ID}/query" \
  -d '{
        "bucketQuery": {
          "clause": {
            "type": "all"
          }
        }
      }'

If Kii Cloud cannot return all query results at once, it will return a pagination key together with a subset of the query results in a response as follows:

{
  "queryDescription": "WHERE ( 1=1 )",
  "results": [....],
  "nextPaginationKey": "200/200"
}

By setting the paginationKey field to the next pagination key when executing the next query, you will get the next set of query results. Set the paginationKey field to the value of the nextPaginationKey field without any change; you cannot specify any custom string as the key even though the key looks like a page number specifier.

curl -v -X POST \
  -H "Authorization: Bearer {ACCESS_TOKEN}" \
  -H "Content-Type: application/vnd.kii.QueryRequest+json" \
  "https://api.kii.com/api/apps/{APP_ID}/users/me/buckets/{BUCKET_ID}/query" \
  -d '{
        "bucketQuery": {
          "clause": {
            "type": "all"
          }
        },
        "paginationKey": "200/200"
      }'

If a response includes another next pagination key, execute the query again with the new key. Otherwise, it means that you've received all query results.

A response might include the next pagenation key when actually there are not any. It can happen when the number of KiiObjects returned from the server at a time and the number of KiiObjects that meet the query condition are the same. You should be aware that an empty result might be returned when an attempt is made to get a next page.

Note: Kii Cloud does not take a snapshot of query results. If there is some time between query requests using pagination, the results may be skewed.