where Clause

Example

Example below will return records where studentId=50.

curl -X POST \
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" \
  -H "Content-Type: application/json" \
  -d '{"where":{"studentId": 50}}' \
  https://api.appery.io/rest/1/db/collections/todo/query

[
    {
        "_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 POST \
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" \
  -H "Content-Type: application/json" \
  -d '{"where":{"studentId": {"$lt": 50}}}' \
  https://api.appery.io/1/db/collections/students/query

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 POST \
  -H "X-Appery-Database-Id: 526fdbd8e4b07c3286b537f1" \
  -H "Content-Type: application/json" \
  -d '{"where":{"studentId": {"$lte": 50}}}' \
  https://api.appery.io/1/db/collections/students/query

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 POST \
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" \
  -H "Content-Type: application/json" \
  -d '{"where":{"studentId": {"$gt": 50}}}' \
  https://api.appery.io/1/db/collections/student/query

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 POST \
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" \
  -H "Content-Type: application/json" \
  -d '{"where":{"studentId": {"$gte": 50}}}' \
  https://api.appery.io/1/db/collections/students/query

Not equal to ($ne)

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

curl -X POST \
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" \
  -H "Content-Type: application/json" \
  -d '{"where":{"studentId": {"$ne": 20}}}' \
  https://api.appery.io/1/db/collections/students/query

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 POST \
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" \
  -H "Content-Type: application/json" \
  -d '{"where":{"studentId": {"$in": [20,80]}}}' \
  https://api.appery.io/1/db/collections/students/query

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 POST \
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" \
  -H "Content-Type: application/json" \
  -d '{"where":{"studentId": {"$nin": [20,80]}}}' \
  https://api.appery.io/1/db/collections/students/query

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 POST \
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" \
  -H "Content-Type: application/json" \
  -d '{"where":{"studentId": {"$exists":true}}}' \
  https://api.appery.io/1/db/collections/students/query

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 POST \
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" \
  -H "Content-Type: application/json" \
  -d '{"where":{"studentId": {"$exists":false}}}' \
  https://api.appery.io/1/db/collections/students/query

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 POST \
  -H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9" \
  -H "Content-Type: application/json" \
  -d '{"where":{"studentId": {"$not":{"$gt":30 }}}}' \
  https://api.appery.io/1/db/collections/students/query