where Clause

Query constraints

It’s possible to specify a where clause in the request. The value of the where parameter needs to be in JSON format. As you’re sending a GET request, the JSON value is also then URL encoded.

Example

Example below will return records where studentId=50.

curl -X GET 
-H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" 
-d 'where={"studentId": 50}' 
https://api.appery.io/rest/1/db/collections/todo
[
    {
        "_id":"5278cb01e4b01085e4b7945d",
        "_createdAt":"2013-11-05 10:40:01.184",
        "_updatedAt":"2015-06-18 12:06:10.177",
        "marks":[
            7.0,
            6.0,
            5.0
        ],
        "studentId":50.0,
        "studentName":"Dan"
    }
]
The where parameter also supports the following comparison values:

  • $lt – less than.
  • $lte – less than or equal to.
  • $gt – greater than.
  • $gte – greater than or equal to.
  • $ne – not equal to.
  • $in – contained in.
  • $nin – not contained in.
  • $exists – a value is set for the key.
  • $not – not match the expression.

Less than ($lt)

If you want to get only the students where the studentId is less than 50 ($lt):

curl -X GET 
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" 
  -d 'where={"studentId": {"$lt": 50}}'
  https://api.appery.io/1/db/collections/students
The result is only the students with student ID of 10, 20, 30, and 40.

Less than or equal to ($lte)

If you want to get only students where the studentId is less than or equal to 50 ($lte):

curl -X GET 
  -H "X-Appery-Database-Id: 526fdbd8e4b07c3286b537f1" 
  -d 'where={"studentId": {"$lte": 50}}' 
  https://api.appery.io/1/db/collections/students
The result is the same as the previous example, except now it also includes 50.

Greater than ($gt)

Going the opposite way, if you want to show all students with a studentId of greater than 50 ($gt):

curl -X GET
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" 
  -d 'where={"studentId": {"$gt": 50}}' 
  https://api.appery.io/1/db/collections/student
This would return objects with IDs of 60, 70, 80, 90, and 100.

Greater than or equal to ($gte)

Using greater than or equal to ($gte) is very similar:

curl -X GET 
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" 
  -d 'where={"studentId": {"$gte": 50}}' 
  https://api.appery.io/1/db/collections/students
Not equal to ($ne)

By using not equal to ($ne), you can specify which object you don’t want to select:

curl -X GET 
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" 
  -d 'where={"studentId": {"$ne": 20}}' 
  https://api.appery.io/1/db/collections/students
You will get all the student objects except the student with the ID of 20.

Contained in ($in)

To get specific objects, use contained in ($in):

curl -X GET 
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" 
  -d 'where={"studentId": {"$in": [20,80]}}' 
  https://api.appery.io/1/db/collections/students
This will return only student objects with IDs of 20 and 80

Not contained in ($nin)

The opposite of contained in ($in) is not contained in ($nin):

curl -X GET 
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" 
  -d 'where={"studentId": {"$nin": [20,80]}}' 
  https://api.appery.io/1/db/collections/students
This would return all objects besides objects with IDs of 20 and 80.

A value is set ($exists)

$exists option checks whether a value is set for a column:

curl -X GET 
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" 
  -d 'where={"studentId": {"$exists":true}}' 
  https://api.appery.io/1/db/collections/students
As every object has the ID set, the above query would return the entire list. Or, specifying false. It would return an empty list, as every object has the student ID set.
curl -X GET 
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" 
  -d 'where={"studentId": {"$exists":false}}' 
  https://api.appery.io/1/db/collections/students
Not match the expression ($not)

$not performs a logical NOT operation on the specified operator expression and selects the documents that do not match the operator expression.

This request will return all records where studentId is NOT greater than 30.

curl -X GET 
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" 
  -d 'where={ "studentId": { "$not": { "$gt":30 } } }' 
  https://api.appery.io/1/db/collections/students