Query Examples

See the following examples of various queries:

Querying all KiiObjects in a bucket

Let us start with the simplest example: querying all KiiObjects in a bucket.

To get all KiiObjects in a bucket, you first create an "all" query using the KiiQuery.queryWithClause() without a KiiClause object, then call the executeQuery() method of the target bucket with the "all" query. Alternatively, you can call the method with a value of null as a query.

  • // Prepare the target bucket to be queried.
    var bucket = Kii.bucketWithName("AppBucket");
    
    // Create a query that returns all KiiObjects.
    var allQuery = KiiQuery.queryWithClause();
    
    // Define callback functions.
    var queryCallbacks = {
      success: function(queryPerformed, resultSet, nextQuery) {
        // Do something with the result.
        for (var i = 0; i < resultSet.length; i++) {
          // Do something with the KiiObject at resultSet[i].
        }
        // If there is more data to retrieve
        if (nextQuery != null) {
          // Execute another query to get more KiiObjects.
          bucket.executeQuery(nextQuery, queryCallbacks);
        }
      },
      failure: function(queryPerformed, errorString) {
        // Handle the error.
      }
    }
    
    // Query KiiObjects.
    bucket.executeQuery(allQuery, queryCallbacks);
    // Alternatively, you can use:
    // bucket.executeQuery(null, queryCallbacks);
  • // Prepare the target bucket to be queried.
    var bucket = Kii.bucketWithName("AppBucket");
    
    // Create a query that returns all KiiObjects.
    var allQuery = KiiQuery.queryWithClause();
    
    function queryAll(bucket, query, userProc) {
      var queryRecurr = function(params) {
        var queryPerformed = params[0];
        var resultSet = params[1];
        var nextQuery = params[2];
    
        userProc(resultSet);
        if (nextQuery == null) {
          return Promise.resolve();
        }
        // There is more data to retrieve.
        // Execute another query to get more KiiObjects.
        return bucket.executeQuery(nextQuery).then(queryRecurr);
      };
      return bucket.executeQuery(query).then(queryRecurr);
    }
    
    // Query KiiObjects.
    queryAll(bucket, allQuery, function(records) {
      // Do something with the result.
      for (var i = 0; i < records.length; i++) {
        // Do something with the KiiObject at resultSet[i].
      }
    }).catch(
      function(error) {
        var theBucket = error.target;
        var errorString = error.message;
        // Handle the error.
      }
    );

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

Querying with multiple conditions

In the next example, we will be querying KiiObjects with the following conditions:

  • The property "gender" has a value of "Female".
  • The property "age" is greater than 18.
  • Sort the query results in ascending order by the property "age".
  • Limit the number of query results returned per call to 10.

The following code illustrates how you can execute such a query.

  • // Prepare the target bucket to be queried.
    var bucket = Kii.bucketWithName("AppBucket");
    
    // Define clauses.
    var clause1 = KiiClause.equals("gender", "Female");
    var clause2 = KiiClause.greaterThan("age", 18);
    
    // Define an And clause with the above clauses.
    var totalClause = KiiClause.and(clause1, clause2);
    
    // Create a query with the And clause.
    var query = KiiQuery.queryWithClause(totalClause);
    
    // Define how to output the query result.
    query.setLimit(10);
    query.sortByAsc("age");
    
    // Define callback functions.
    var queryCallbacks = {
      success: function(queryPerformed, resultSet, nextQuery) {
        // Do something with the result.
        for (var i=0; i<resultSet.length; i++) {
          // Do something with the KiiObject at resultSet[i].
        }
        // If there is more data to retrieve
        if (nextQuery != null) {
          // Query the next 10 KiiObjects with pagination.
          bucket.executeQuery(nextQuery, queryCallbacks);
        }
      },
      failure: function(queryPerformed, errorString) {
        // Handle the error.
      }
    }
    
    // Query KiiObjects.
    bucket.executeQuery(query, queryCallbacks);
  • // Prepare the target bucket to be queried.
    var bucket = Kii.bucketWithName("AppBucket");
    
    // Define clauses.
    var clause1 = KiiClause.equals("gender", "Female");
    var clause2 = KiiClause.greaterThan("age", 18);
    
    // Define an And clause with the above clauses.
    var totalClause = KiiClause.and(clause1, clause2);
    
    // Create a query with the And clause.
    var query = KiiQuery.queryWithClause(totalClause);
    
    // Define how to output the query result.
    query.setLimit(10);
    query.sortByAsc("age");
    
    function queryAll(bucket, query, userProc) {
      var queryRecurr = function(params) {
        var queryPerformed = params[0];
        var resultSet = params[1];
        var nextQuery = params[2];
    
        userProc(resultSet);
        if (nextQuery == null) {
          return Promise.resolve();
        }
        // There is more data to retrieve.
        // Query the next 10 KiiObjects with pagination.
        return bucket.executeQuery(nextQuery).then(queryRecurr);
      };
      return bucket.executeQuery(query).then(queryRecurr);
    }
    
    // Query KiiObjects.
    queryAll(bucket, query, function(records) {
      // Do something with the result.
      for (var i = 0; i < records.length; i++) {
        // Do something with the KiiObject at resultSet[i].
      }
    }).catch(
      function(error) {
        var theBucket = error.target;
        var errorString = error.message;
        // Handle the error.
      }
    );

Querying with geolocation data

Let us now see how to query using geolocation data as a query condition.

Suppose that a KiiObject has a field named "location" whose value is a GeoPoint object as shown in the following sample code:

  • // Create a KiiObject.
    var obj = KiiUser.getCurrentUser().bucketWithName("MyBucket").createObject();
    var point = KiiGeoPoint.geoPoint(35.661561, 139.769595);
    obj.setGeoPoint("location",point);
    
    // Save the KiiObject.
    obj.save({
      success: function(theObject) {
        // Do something.
      },
      failure: function(theObject, errorString) {
        // Handle the error.
      }
    });
  • // Create a KiiObject.
    var obj = KiiUser.getCurrentUser().bucketWithName("MyBucket").createObject();
    var point = KiiGeoPoint.geoPoint(35.661561, 139.769595);
    obj.setGeoPoint("location",point);
    
    // Save the KiiObject.
    obj.save().then(
      function(theObject) {
        // Do something.
      }
    ).catch(
      function(error) {
        var theObject = error.target;
        var errorString = error.message;
        // Handle the error.
      }
    );

Here is an example that queries KiiObjects within a rectangle area using a GeoBox() clause.

  • // Prepare the target bucket to be queried.
    var bucket = KiiUser.getCurrentUser().bucketWithName("MyBucket");
    
    // Define a GeoBox clause with northeast and southwest points.
    var ne = KiiGeoPoint.geoPoint(36.069082, 140.07843);
    var sw = KiiGeoPoint.geoPoint(35.52105, 139.699402);
    var clause = KiiClause.geoBox('location', ne, sw);
    
    // Create a query with the GeoBox clause.
    var query = KiiQuery.queryWithClause(clause);
    
    // Define callback functions.
    var queryCallbacks = {
      success: function(queryPerformed, resultSet, nextQuery) {
        // Do something with the result.
        for (var i = 0; i < resultSet.length; i++) {
          // Do something with the KiiObject at resultSet[i].
        }
        // If there is more data to retrieve
        if (nextQuery != null) {
          // Execute another query to get more KiiObjects.
          bucket.executeQuery(nextQuery, queryCallbacks);
        }
      },
      failure: function(queryPerformed, errorString) {
        // Handle the error.
      }
    }
    
    // Query KiiObjects.
    bucket.executeQuery(query, queryCallbacks);
  • // Prepare the target bucket to be queried.
    var bucket = KiiUser.getCurrentUser().bucketWithName("MyBucket");
    
    // Define a GeoBox clause with northeast and southwest points.
    var ne = KiiGeoPoint.geoPoint(36.069082, 140.07843);
    var sw = KiiGeoPoint.geoPoint(35.52105, 139.699402);
    var clause = KiiClause.geoBox('location', ne, sw);
    
    // Create a query with the GeoBox clause.
    var query = KiiQuery.queryWithClause(clause);
    
    function queryAll(bucket, query, userProc) {
      var queryRecurr = function(params) {
        var queryPerformed = params[0];
        var resultSet = params[1];
        var nextQuery = params[2];
    
        userProc(resultSet);
        if (nextQuery == null) {
          return Promise.resolve();
        }
        // There is more data to retrieve.
        // Execute another query to get more KiiObjects.
        return bucket.executeQuery(nextQuery).then(queryRecurr);
      };
      return bucket.executeQuery(query).then(queryRecurr);
    }
    
    // Query KiiObjects.
    queryAll(bucket, query, function(records) {
      // Do something with the result.
      for (var i = 0; i < records.length; i++) {
        // Do something with the KiiObject at resultSet[i].
      }
    }).catch(
      function(error) {
        var theBucket = error.target;
        var errorString = error.message;
        // Handle the error.
      }
    );

As shown in the sample code, you define a GeoBox() clause by calling the geoBox() method with two GeoPoints, one representing the northeast point, and another one representing the southwest point.

Here is an example of querying KiiObjects within a circular area using a GeoDistance() clause. In this example, we will query KiiObjects that are in the area overlapped by two GeoDistances (GeoDistance1 and GeoDistance2). We will also sort query results in ascending order by the distance from GeoDistance1's center point.

  • // Prepare the target bucket to be queried.
    var bucket = KiiUser.getCurrentUser().bucketWithName("MyBucket");
    
    // Define the first GeoDistance clause.
    var center1 = KiiGeoPoint.geoPoint(35.658603, 139.745433);
    var clause1 = KiiClause.geoDistance('location', center1, 3000, "distance_from_center1");
    
    // Define the second GeoDistance clause.
    var center2 = KiiGeoPoint.geoPoint(35.681382, 139.766084);
    var clause2 = KiiClause.geoDistance('location', center2, 3000, "distance_from_center2");
    
    // Define an And clause with the above clauses.
    var intersectionClause = KiiClause.and(clause1, clause2);
    
    // Create a query with the GeoDistance clauses and set the sort order.
    var query = KiiQuery.queryWithClause(intersectionClause);
    query.sortByAsc("_calculated.distance_from_center1");
    
    // Define callback functions.
    var queryCallbacks = {
      success: function(queryPerformed, resultSet, nextQuery) {
        // Do something with the result.
        for (var i = 0; i < resultSet.length; i++) {
          // Do something with the KiiObject at resultSet[i].
          // Check the first location.
          var obj = resultSet[i];
          var point = obj.getGeoPoint("location");
    
          // Get the distance from the center to the location.
          var calculated = obj.get("_calculated");
          var distance = calculated["distance_from_center1"];
        }
        // If there is more data to retrieve
        if (nextQuery != null) {
          // Execute another query to get more KiiObjects.
          bucket.executeQuery(nextQuery, queryCallbacks);
        }
      },
      failure: function(queryPerformed, errorString) {
        // Handle the error.
      }
    }
    
    // Query KiiObjects.
    bucket.executeQuery(query, queryCallbacks);
  • // Prepare the target bucket to be queried.
    var bucket = KiiUser.getCurrentUser().bucketWithName("MyBucket");
    
    // Define the first GeoDistance clause.
    var center1 = KiiGeoPoint.geoPoint(35.658603, 139.745433);
    var clause1 = KiiClause.geoDistance('location', center1, 3000, "distance_from_center1");
    
    // Define the second GeoDistance clause.
    var center2 = KiiGeoPoint.geoPoint(35.681382, 139.766084);
    var clause2 = KiiClause.geoDistance('location', center2, 3000, "distance_from_center2");
    
    // Define an And clause with the above clauses.
    var intersectionClause = KiiClause.and(clause1, clause2);
    
    // Create a query with the GeoDistance clauses and set the sort order.
    var query = KiiQuery.queryWithClause(intersectionClause);
    query.sortByAsc("_calculated.distance_from_center1");
    
    function queryAll(bucket, query, userProc) {
      var queryRecurr = function(params) {
        var queryPerformed = params[0];
        var resultSet = params[1];
        var nextQuery = params[2];
    
        userProc(resultSet);
        if (nextQuery == null) {
          return Promise.resolve();
        }
        // There is more data to retrieve.
        // Execute another query to get more KiiObjects.
        return bucket.executeQuery(nextQuery).then(queryRecurr);
      };
      return bucket.executeQuery(query).then(queryRecurr);
    }
    
    // Query KiiObjects.
    queryAll(bucket, query, function(records) {
      // Do something with the result.
      for (var i = 0; i < records.length; i++) {
        // Do something with the KiiObject at resultSet[i].
        // Check the first location.
        var obj = records[i];
        var point = obj.getGeoPoint("location");
    
        // Get the distance from the center to the location.
        var calculated = obj.get("_calculated");
        var distance = calculated["distance_from_center1"];
      }
    }).catch(
      function(error) {
        var theBucket = error.target;
        var errorString = error.message;
        // Handle the error.
      }
    );

Here is what is happening in the sample code:

  • Defines two GeoDistance query conditions by calling the geoDistance() method. Here we specify a GeoPoint representing the center, a radius in meters, and optionally a field that stores the distance between the center of the circular area and a returned KiiObject. Kii Cloud will use this field in a query response.
  • Creates a KiiClause instance by concatenating two GeoDistance query conditions via the and() method. Then creates a KiiQuery instance using this KiiClause instance.
  • Calls the sortByAsc() method of the KiiQuery instance to set the sort order.
  • Queries KiiObjects by calling the executeQuery() method of the target bucket by passing the query instance.
  • Parses the query results.

With a GeoDistance query condition, Kii Cloud can return the distance between the center point of a GeoPoint and each returned KiiObject. In this example, the distance between GeoDistance1's center point and KiiObjects are returned.

  • The distance will be stored in the field specified when the geoDistance() method is called (the "distance_from_center1" field in this example).
  • To sort query results by distance, call the sortByAsc() method with a string "_calculated." followed by the name of the distance field.
  • To get the distance, get the value of the "_calculated" field by calling the get() method, and then get the distance from the distance field.

Querying with predefined keys

Now, let us show an example of querying with predefined keys.

The next sample code gets KiiObjects that are owned by the current user and are either not updated after creation or updated within a day.

  • // Prepare the target bucket to be queried.
    var bucket = Kii.bucketWithName("AppBucket");
    
    // Get values to use in query conditions.
    var userId = KiiUser.getCurrentUser().getID()
    var withinOneDay = new Date().getTime() - 24 * 60 * 60 * 1000;
    
    // Create a query with clauses using predefined keys and the retrieved values.
    var query = KiiQuery.queryWithClause(KiiClause.and(
          KiiClause.equals("_owner", userId),
          KiiClause.or(
              KiiClause.equals("_version", 1),
              KiiClause.greaterThan("_created", withinOneDay))));
    
    // Define how to output the query result.
    query.setLimit(10);
    query.sortByAsc("_created");
    
    // Define callback functions.
    var queryCallbacks = {
      success: function(queryPerformed, resultSet, nextQuery) {
        // Do something with the result.
        for (var i = 0; i < resultSet.length; i++) {
          // Do something with the KiiObject at resultSet[i].
        }
        // If there is more data to retrieve
        if (nextQuery != null) {
          // Query the next 10 KiiObjects with pagination.
          bucket.executeQuery(nextQuery, queryCallbacks);
        }
      },
      failure: function(queryPerformed, errorString) {
        // Handle the error.
      }
    }
    
    // Query KiiObjects.
    bucket.executeQuery(query, queryCallbacks);
  • // Prepare the target bucket to be queried.
    var bucket = Kii.bucketWithName("AppBucket");
    
    // Get values to use in query conditions.
    var userId = KiiUser.getCurrentUser().getID()
    var withinOneDay = new Date().getTime() - 24 * 60 * 60 * 1000;
    
    // Create a query with clauses using predefined keys and the retrieved values.
    var query = KiiQuery.queryWithClause(KiiClause.and(
          KiiClause.equals("_owner", userId),
          KiiClause.or(
              KiiClause.equals("_version", 1),
              KiiClause.greaterThan("_created", withinOneDay))));
    
    // Define how to output the query result.
    query.setLimit(10);
    query.sortByAsc("_created");
    
    function queryAll(bucket, query, userProc) {
      var queryRecurr = function(params) {
        var queryPerformed = params[0];
        var resultSet = params[1];
        var nextQuery = params[2];
    
        userProc(resultSet);
        if (nextQuery == null) {
          return Promise.resolve();
        }
        // There is more data to retrieve.
        // Query the next 10 KiiObjects with pagination.
        return bucket.executeQuery(nextQuery).then(queryRecurr);
      };
      return bucket.executeQuery(query).then(queryRecurr);
    }
    
    // Query KiiObjects.
    queryAll(bucket, query, function(records) {
      // Do something with the result.
      for (var i = 0; i < records.length; i++) {
        // Do something with the KiiObject at resultSet[i].
      }
    }).catch(
      function(error) {
        var theBucket = error.target;
        var errorString = error.message;
        // Handle the error.
      }
    );

Querying with a specific field name and a field type

Now, let us show an example of querying with a specific field name and a field type. KiiObjects can have various custom fields that are not always consistent. By using a hasField() clause, we can narrow the results to KiiObjects that have a specific field of a specific data type.

The next sample code gets KiiObjects that have an optional promotionalCode field.

  • // Prepare the target bucket to be queried.
    var bucket = Kii.bucketWithName("AppBucket");
    
    // Create a query with a clause using a specific field and a type.
    var hasFieldQuery = KiiQuery.queryWithClause(KiiClause.hasField("promotionalCode","STRING"));
    
    // Define callback functions.
    var queryCallbacks = {
      success: function(queryPerformed, resultSet, nextQuery) {
        // Do something with the result.
        for (var i = 0; i < resultSet.length; i++) {
          // Do something with the KiiObject at resultSet[i].
        }
        // If there is more data to retrieve
        if (nextQuery != null) {
          // Execute another query to get more KiiObjects.
          bucket.executeQuery(nextQuery, queryCallbacks);
        }
      },
      failure: function(queryPerformed, errorString) {
        // Handle the error.
      }
    }
    
    // Query KiiObjects.
    bucket.executeQuery(hasFieldQuery, queryCallbacks);
  • // Prepare the target bucket to be queried.
    var bucket = Kii.bucketWithName("AppBucket");
    
    // Create a query with a clause using a specific field and a type.
    var hasFieldQuery = KiiQuery.queryWithClause(KiiClause.hasField("promotionalCode","STRING"));
    
    function queryAll(bucket, query, userProc) {
      var queryRecurr = function(params) {
        var queryPerformed = params[0];
        var resultSet = params[1];
        var nextQuery = params[2];
    
        userProc(resultSet);
        if (nextQuery == null) {
          return Promise.resolve();
        }
        // There is more data to retrieve.
        // Execute another query to get more KiiObjects.
        return bucket.executeQuery(nextQuery).then(queryRecurr);
      };
      return bucket.executeQuery(query).then(queryRecurr);
    }
    
    // Query KiiObjects.
    queryAll(bucket, hasField, function(records) {
      // Do something with the result.
      for (var i = 0; i < records.length; i++) {
        // Do something with the KiiObject at resultSet[i].
      }
    }).catch(
      function(error) {
        var theBucket = error.target;
        var errorString = error.message;
        // Handle the error.
      }
    );

Querying with a not clause

Now, let us show an example of querying with a not() clause. This time, we will query KiiObjects that are outside a specified rectangle area.

  • // Prepare the target bucket to be queried.
    var bucket = KiiUser.getCurrentUser().bucketWithName("MyBucket");
    
    // Define a GeoBox clause with northeast and southwest points.
    var ne = KiiGeoPoint.geoPoint(36.069082, 140.07843);
    var sw = KiiGeoPoint.geoPoint(35.52105, 139.699402);
    var geoBoxClause = KiiClause.geoBox('location', ne, sw);
    
    // Define a not clause with the Geobox clause.
    var notInTheBoxClause = KiiClause.not(geoBoxclause);
    
    // Create a query with the not clause.
    var query = KiiQuery(notInTheBoxClause);
    
    // Define callback functions.
    var queryCallbacks = {
      success: function(queryPerformed, resultSet, nextQuery) {
        // Do something with the result.
        for (var i = 0; i < resultSet.length; i++) {
          // Do something with the KiiObject at resultSet[i].
        }
        // If there is more data to retrieve
        if (nextQuery != null) {
          // Execute another query to get more KiiObjects.
          bucket.executeQuery(nextQuery, queryCallbacks);
        }
      },
      failure: function(queryPerformed, errorString) {
        // Handle the error.
      }
    }
    
    // Query KiiObjects.
    bucket.executeQuery(query, queryCallbacks);
  • // Prepare the target bucket to be queried.
    var bucket = KiiUser.getCurrentUser().bucketWithName("MyBucket");
    
    // Define a GeoBox clause with northeast and southwest points.
    var ne = KiiGeoPoint.geoPoint(36.069082, 140.07843);
    var sw = KiiGeoPoint.geoPoint(35.52105, 139.699402);
    var geoBoxClause = KiiClause.geoBox('location', ne, sw);
    
    // Define a not clause with the Geobox clause.
    var notInTheBoxClause = KiiClause.not(geoBoxclause);
    
    // Create a query with the not clause.
    var query = KiiQuery(notInTheBoxClause);
    
    function queryAll(bucket, query, userProc) {
      var queryRecurr = function(params) {
        var queryPerformed = params[0];
        var resultSet = params[1];
        var nextQuery = params[2];
    
        userProc(resultSet);
        if (nextQuery == null) {
          return Promise.resolve();
        }
        // There is more data to retrieve.
        // Execute another query to get more KiiObjects.
        return bucket.executeQuery(nextQuery).then(queryRecurr);
      };
      return bucket.executeQuery(query).then(queryRecurr);
    }
    
    // Query KiiObjects.
    queryAll(bucket, query, function(records) {
      // Do something with the result.
      for (var i = 0; i < records.length; i++) {
        // Do something with the KiiObject at resultSet[i].
      }
    }).catch(
      function(error) {
        var theBucket = error.target;
        var errorString = error.message;
        // Handle the error.
      }
    );

Querying with a not() clause 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.