Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: fixed malformed links

...

Note

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.

...

The following parameters are available for query operations:

  • count
  • limit and skip
  • id and _id
  • where
  • order
  • sel
  • unsel
  • page
  • per_page

 

Table of Content Zone
locationtop

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

Note

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

Note

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. Default value of limit is 10.

id and _id

query by id or _id:

Table Cell (td)
Code Block
linenumberstrue
languagejs
collapsetrue
~ curl -d 'where={"id":"54517c49dda09594270002da"}' -X GET "localhost:8082/v1/users/query.json?key=l1J36VuGo2eFMJ
~ curl -d 'where={"id":"54517c49dda09594270002da"}' -X GET 
"localhost:8082/v1/users/query.json?key=l1J36VuGo2eFMJKELNXdxAq8Z4MQIbYr&pretty_json=true"
{
    "meta": {
        "code": 200,
        "status": "ok",
        "method_name": "queryUsers"
    },
    "response": {
        "users": [{
            "id": "54517c49dda09594270002da",
            "created_at": "2014-10-29T23:46:17+0000",
            "updated_at": "2014-10-29T23:46:17+0000",
            "external_accounts": [],
            "confirmed_at": "2014-10-29T23:46:17+0000",
            "username": "hmm",
            "email": "",
            "admin": "false",
            "stats": {
                "photos": {
                    "total_count": 0
                },
                "storage": {
                    "used": 0
                }
            },
            "custom_fields": {}
        }]
    }
}

~ curl -d 'where={"_id":"54517c49dda09594270002da"}' -X GET 
"localhost:8082/v1/users/query.json?key=l1J36VuGo2eFMJKELNXdxAq8Z4MQIbYr&pretty_json=true"
{
    "meta": {
        "code": 200,
        "status": "ok",
        "method_name": "queryUsers"
    },
    "response": {
        "users": [{
            "id": "54517c49dda09594270002da",
            "created_at": "2014-10-29T23:46:17+0000",
            "updated_at": "2014-10-29T23:46:17+0000",
            "external_accounts": [],
            "confirmed_at": "2014-10-29T23:46:17+0000",
            "username": "hmm",
            "email": "",
            "admin": "false",
            "stats": {
                "photos": {
                    "total_count": 0
                },
                "storage": {
                    "used": 0
                }
            },
            "custom_fields": {}
        }]
    }
}

Query multiple objects by using $in:

Table Cell (td)
Code Block
linenumberstrue
languagejs
collapsetrue
~ curl -d 'where={"_id":{"$in":["54517c49dda09594270002da","54517c33dda09594270002d5"]}}' -X GET 
"localhost:8082/v1/users/query.json?key=l1J36VuGo2eFMJKELNXdxAq8Z4MQIbYr&pretty_json=true"
{
    "meta": {
        "code": 200,
        "status": "ok",
        "method_name": "queryUsers"
    },
    "response": {
        "users": [{
            "id": "54517c49dda09594270002da",
            "created_at": "2014-10-29T23:46:17+0000",
            "updated_at": "2014-10-29T23:46:17+0000",
            "external_accounts": [],
            "confirmed_at": "2014-10-29T23:46:17+0000",
            "username": "hmm",
            "email": "",
            "admin": "false",
            "stats": {
                "photos": {
                    "total_count": 0
                },
                "storage": {
                    "used": 0
                }
            },
            "custom_fields": {}
        }, {
            "id": "54517c33dda09594270002d5",
            "created_at": "2014-10-29T23:45:55+0000",
            "updated_at": "2014-10-29T23:45:55+0000",
            "external_accounts": [],
            "confirmed_at": "2014-10-29T23:45:55+0000",
            "username": "test",
            "email": "",
            "admin": "false",
            "stats": {
                "photos": {
                    "total_count": 0
                },
                "storage": {
                    "used": 0
                }
            },
            "custom_fields": {}
        }]
    }
}

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 inside the array or object.

For more information, see Supported Data Types.

Currently, ArrowDB does not support case insensitive query. To perform 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:

Table Cell (td)
Code Block
languagejs
where={"first_name": "joe"}

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:

Table Cell (td)
Code Block
languagejs
where={"age": 28, "favorite_color": "blue", "first_name" : "joe"}

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 hypens (-). 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 general creation of custom objects and field queries.

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

Code Block
languagejs
where={"age": {"$gt":28}}

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

Code Block
languagejs
where = {"age": {"$in":[28,38]}}

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

Code Block
languagejs
where = {"age": {"$nin":[28,38]}}

If you want to find a user whose email is "

...

john@example.com" and type is User:

Table Cell (td)
Code Block
languagejs
where={"$and": [ {"email" : "john@example.com"}, { "_type" : "User" } ] }

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

Code Block
languagejs
where={"options": {"$all" : [2,3] } }

If you want to find users who have location information.

Code Block
languagejs
where={"location": {"$exists" : true} }

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

Code Block
languagejs
where={"location": {"$exists" : false} }

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:

Code Block
languagejs
where={"scores":{ "$elemMatch": { "$gte": 70, "$lt": 90 } }}

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.

Code Block
languagejs
where={"tags":{ "$all": [ 'employee', 'manager' ] } 

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.

Table Cell (td)
Code Block
languagejs
where={"first_name":"joe", "coordinates":{"$nearSphere":[-122.1, 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)

Table Cell (td)
Code Block
languagejs
where={"first_name":"joe", "coordinates":{"$nearSphere":[-122.1,37.1], "$maxDistance" : 0.00126}}

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

Table Cell (td)
Code Block
languagejs
where={"$text": { "$search": "javascript,ruby,python,-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:

Code Block
languagejs
order=first_name,-created_at

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:

Code Block
languagejs
sel={"all":["first_name"]}

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:

Code Block
languagejs
unsel={"all":["first_name"]}


Query pagination

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

...