Would you like to contribute to the Titanium docs? To get started, sign up for an account on the Appcelerator Wiki and sign our CLA.

Skip to end of metadata
Go to start of metadata

ArrowDB provides APIs for querying and searching ArrowDB objects. The query APIs allow you to perform custom database-style searches, while search APIs perform a full text search using the ArrowDB search engine.

Icon

The search API is deprecated since Release 1.3.0. Applications created before Arrow Cloud Release 1.3.0 can continue to use the deprecated search API, but new applications will need to use the query API with the  $text  query operator.

Query API overview

The query API provides an interface for applying database-style query constraints on predefined objects and custom fields.

When no query parameters are provided, all objects of the specified type are returned with default pagination. You can control pagination of queries with the skip and limit parameters, or by using a custom where clause. See Query Pagination for more information.

You can also control the sort order of query results using the order parameter, and specify the fields you want to include (or exclude) from results using the sel and unsel query parameters.

Query API availability

The following ArrowDB object provide query methods:  ACLsChatsCheckinsCustomObjectsEventsFilesGeoFencesKeyValuesLikesLogsMessagesPhotosPlacesPosts
PushNotificationsPushSchedulesReviewsStatuses, and Users.

For security reasons, when querying for Users the email field is not returned in the User object unless you have admin access.

Query parameters

The following parameters are available for query operations:

 

count

If the query includes count=true , the response's meta object contains a  count  field whose value is the total number of objects that matched the query criteria. If the query matches more than 5000 objects, the count field contains the value "5000+". For more information, see Query Pagination.

page

Icon

Starting in ArrowDB 1.1.5, page and per_page are no longer supported in query operations. Applications should instead use skip and limit query parameters.

per_page

Icon

Starting in ArrowDB 1.1.5, page and per_page are no longer supported in query operations. Applications should instead use skip and limit query parameters.

limit

The number of records to fetch. The value must be greater than 0, and no greater than 1000, or an HTTP 400 (Bad Request) error will be returned. The default value of limit is 10.

id and _id

query by id or _id:

 Expand source

Query multiple objects by using $in:

 Expand source

new_pagination

When new_pagination=true is included in a query, the number of records evaluated in the database is limited to 5000.

By default, ArrowDB puts no limit on the number of records that are evaluated in the database. This means, for example, that if you execute an open-ended query for all ArrowDB objects of some type, and apply a sort and filter on the results, all records in the database are evaluated, sorted, and filtered in memory.

For applications that have relatively small numbers of total records (< 5000), this operation can by completed efficiently with no noticeable impact on your application's performance. However, if a table has a very large number (>= 1 million) records these kinds of operations are very inefficient, and your users will notice a performance hit.

skip

The number of records to skip. The value must be greater than or equal to 0, and no greater than 4999, or an HTTP 400 error will be returned. To skip 5000 records or more you need to perform a range-based query. See Query Pagination for more information.

where

Constrains values for fields. The value should be encoded JSON. Each value in the search query needs to be less that 1024 bytes. If the value is larger than 1024 bytes, the query does not return any results.

Each type of ArrowDB object has a set of predefined fields that can be queried with the where operator. See each object's individual query method for details.

In addition, you can query the custom fields on any object. Note, however, that you can only query simple values, such as Strings, Dates, Numbers, or Booleans. If a custom field takes an array or object as a value, you can't query any of the values stored in the array or object.

For more information, see Supported Data Types.

Currently, ArrowDB does not support case insensitive query. To perform a case-insensitive query on a field, save an additional normalized copy of the original field and perform the query on the normalized field instead.

To perform an exact match on a field, for example, to search for users with first_name matching "joe", use:

You can add more search criteria by adding them together. For example, to search for users with first_name matching "joe", favorite_color matching "blue" and age of 28, use the following query:

For non-exact matches, where supports these options:

OperatorSummary
$ltLess than
$lteLess than or equal to
$gtGreater than
$gteGreater than or equal to
$neNot equal to
$inContained in, allows you to specify an array of possible matches
$ninNot contained in, it selects objects for which the specified field does not have any value in the specified array
$orUse boolean or in a query, an array of expressions, any of which can match
$norA boolean or expression to do queries, you give $nor a list of expressions, none of which can match the query
$andGive $and an array of expressions, all of which must match the query
$allThe $all operator is similar to $in, but instead of matching any value in the specified array all values in the array must be matched
$elemMatchGive $elemMatch an expression to match against elements in an array
$existsCheck for existence of a field
$regexRegex match on a string. Currently, only prefix matches are supported: the regular expression must begin with an anchor (^) followed by a letter or digit. For example, '^a', '^a.*', and '^a.*$' are allowed, but not '^.*a*'.
$textPerform a text search on the contents of the field. A $text expression has the following syntax: 
{ "$text": { "$search": <string> }} 
Most punctuation marks and spaces are treated as delimiters allowing you to search for multiple keywords, excluding escaped double quotes (\") and hyphens (-). Escaped double quotes are used for phrase searches and hyphens are used to negate searches. Sorting does not work with the $text operator, that is, using the order field will have no effect when using the $text operator.
Geographic coordinates

For querying geographic coordinates, the following operators are supported:

OperatorSummary
$nearSphere  Search near geographic coordinates, format is [longitude, latitude]
$maxDistanceused with $nearSphere to limit maximum search distance. All distances use radians. This allows you to easily multiply by the radius of the earth (about 6371 km or 3959 miles) to get the distance in your choice of units. Conversely, divide by the radius of the earth when doing queries

You can combine any of the above to build a more complex query.

For more information on constructing geographic coordinate queries, see Geographic Coordinates in Custom Fields and Custom Objects & Fields for a general creation of custom objects and field queries.

If you want to find users with age older than 28:

If you want to find users at age 28 or 38:

If you want to find users at age neither 28 nor 38:

If you want to find a user whose email is "john@example.com" and type is User:

If you want to find users who have options are 2,3

If you want to find users who have location information.

If you want to find users who haven't location information.

If you have a custom array with your user object, such as a list of scores (scores: [65, 86, 92'), you can search the elements in the array. For example, if you want to search for scores within a certain range:

If you have a custom array of tags, such as tags: [ 'employee', 'manager' ], you can use the $all operator to return objects that contain all the specified elements.

If you have assigned custom coordinates to your user objects, you can search by users' coordinates. For example, if you want to find users named joe near longitude -122.1 and latitude 37.1.

To find users named joe near longitude -122.1 and latitude 37.1 with maximum distance of 5 miles (convert 5 miles to radians, 5/3959 = 0.00126)

To search for the keywords javascriptruby or python, but not php:

order

Sort results by one or more fields. In general, you can sort based on any predefined field that you can query using the where operator, as well as on custom fields. Any exceptions to this rule are noted in API reference for the individual query methods.

To reverse the sorting order, simply add - in front of a field. For example, to sort results by first_name in ascending order then created_at in descending order:

sel

Selects which fields to return from the query. Do not use this parameter if you are using the unsel parameter.

Assign an array of field names to filter to the all field to search all JSON fields including fields in nested objects. Currently, this is the only supported option.

If you want to display a field from the custom_field object, simply pass the field name of the custom_field object.

If you want to display a field from a nested object, then both the name of the nested object and field need to be specified.

For example, if you want to only return the first_name field:

unsel

Selects which fields to not return from the query. Do not use this parameter if you are using the sel parameter.

Assign an array of field names to filter to the all field to search all JSON fields including fields in nested objects. Currently, this is the only supported option.

If you want to hide displaying a field from the custom_field object, simply pass the field name of the custom_field object.

If you want to hide displaying a field from a nested object, then both the name of the nested object and field need to be specified.

For example, if you want to return all fields except first_name:


Query pagination

Starting with ArrowDB 1.1.5, we have made the following changes:

  • Skip is limited to 0-4999; as a result, you can not skip beyond 5000 records.
  • If the query includes count=true, the query response's meta object contains a count field whose value is the total number of objects that match the query criteria. If the query matches more than 5000 objects, the count field contains the value "5000+". If your query result set includes more than 5000 records, you should perform range-based queries for pagination. This is done by including a where parameter on an object field using the $gt or $lt operators, as discussed below.

For example, the following cURL uses a range-based query for Statuses whose custom field named score is less than 100 and sorts the results in ascending order on the score field:

ArrowDB object IDs, represented by the _id field, are based on object timestamps and machine IDs, which allows for range-based pagination. For example, suppose an application performs a query that whose last object returned has an ID of "5418a8815a6919fde8cf1e4d". To get the set of objects created before that particular object, the application would query for those objects whose _id field is less than that value:

Similarly, if the ID of the first object returned in a query was "5418a87f5a6919fde8cee391" the application would query on objects whose _id field is greater than that value to retrieve the previous set of data:

To query objects between a range of object IDs, use together $gt and $lt together:

For additional examples, see Range-based Query Pagination Examples.

Range-based query pagination examples

Query on custom field, results in ascending order

In this example, the query returns Statuses objects whose custom score field is less than 100, and sorts results on the score in ascending order (&order=score). The query matches 100 total records.

 Expand source

Query on custom field, results in descending order

In this example, Statuses objects are queried whose custom score field is less than 100, and sorts results on score in descending order (&order=-score).

 Expand source

Query for next page of results, results in ascending order

In this example, the next page of Statuses objects are queried whose _id field is less than "53fe1c25759220e9f6754194" and sorted in descending order on the custom score field.

 Expand source

Query for previous page of results

As explained previously, to get the second (or next) page of results in a query, you query for objects whose _id field is less (older) than the _id of the last object returned by the prior query. To get the previous (first) page of results again, you simply query for those objects whose _id is greater (newer) than the first object returned in by the prior query.

However, if you query for the third results page, and want to get the previous (second) results page, you need to restrict the query to those objects whose _id is greater than the first object returned by the most recent query (third results page), and less than that of the last object returned in query before that (second results page); or, equivalently, less than or equal to the first object returned in the initial query (first results page). The following demonstrates this.

First page (page 1)

The first query request doesn't specify a range.

Request:

Response (abbreviated):

Note that the ID of the last object returned in the response ("5447eff680e61e08260002ed").

Next page (page 2)

To get Page 2 results, a where clause is added that limits results to those objects whose _id field is less than the last object in Page 1 results ("5447eff680e61e08260002ed").

Request:

Response (abbreviated):

Note that the first and last IDs of objects returned on Page 2 are "5447efed80e61e08260002ec" and "5447ef9f80e61e081e0002c1", respectively. To get the previous page of results (Page 1) at this point, you could simply query for objects with IDs greater than the first object in the Page 2 results ("5447efed80e61e08260002ec"). In this case, however, the user requests the next page (Page 3) results, as shown below.

Next page (page 3)

To get Page 3 results, the where limits results to those objects whose _id field is less the last object returned in Page 2 results ("5447ef9f80e61e081e0002c1").

Response (abbreviated):

Note that the first object returned has the ID "5446d5d980e61e0826000093".

Previous page (page 2)

At this point, if you query for objects with IDs greater than the first object returned in Page 3, you would, logically, get Page 1 results, not Page 2. Instead, you need to further limit the range to those objects with IDs less than or equal to that of the first object from the Page 2 results; or, equivalently, less than that of the last object id from Page 1 results.

Continuing the example, the request and response (abbreviated) for these approaches are shown below:

Search API overview

Icon

The search API is deprecated since Release 1.3.0. Applications created before Arrow Cloud Release 1.3.0 can continue to use the deprecated search API, but new applications will need to use the query API with the  $text  query operator.

Several ArrowDB objects provide a search API that performs a case-insensitive, full-text search of the given keywords on a list of predefined fields. Please refer to individual object's API documentation for a list of searchable fields. For instance, the Places.search method will search within Places.name and Places.tags.

The Search API is fixed in terms of the searchable fields; use the query API to perform more flexible searches.

Search API parameters

The following parameters are available for search operations:

  • page
  • per_page
  • q

page

Request page number starting from 1. Default is 1.

per_page

Number of results per page. Default is 10.

q

The keyword or phrase to search for.

Search API availability

Search methods are available for the following pre-built ArrowDB objects, as well as for custom fields.