Documentation

Get started with your own restdb.io database.


Querying with the API

Querying your database is an essential part of any application. restdb.io uses plain URLs with simple parameters and JSON documents to query your database.

Query language

Database queries are created as valid JSON documents. A query object consists of fields and operators that make up a complete query.

A simple query example:

{"status": "GOLD"}

Which is the same as asking the database: fetch all records with status GOLD

Simple queries

Match a single field (find Joe):

{"name": "Joe"}

Match multiple fields (find Joe who's 17):

{"name": "Joe", "age": 17}

In addition to query simple fields, you can use special operators to perform more advanced queries.

Logic operators

OperatorDescriptionExample
$notNegation logical operator{"field" : {"$not" : val}}
$inMatch any value in array{"field" : {"$in" : [value1, value2, ...]}
$ninNot match any value in array{"field" : {"$nin" : [value1, value2, ...]}
$regexMatch field{"field" : {"$regex" : ".*"}
$orLogical operator"$or": [{"status": "GOLD"}, {"status": "SILVER"}]}
$andLogical operator"$and": [{"status": "GOLD"}, {"sales": 1000}]}

Conditional operators

OperatorDescriptionExample
$gt>{"salary": {"$gt": 10000}}
$gte>={"salary": {"$gte": 10000}}
$lt<{"salary": {"$lt": 10000}}
$lte<={"salary": {"$lte": 10000}}
$bt>= value <={"salary": {"$bt": [5000, 7500]}}
$existsCheck if field exists{"field": {"$exists": true|false}}
$exists (array)Check if array field exists or is empty{"field.0": {"$exists": true|false}}
$elemMatchMatch element in array{"field": {"$elemMatch" : {"name": 'Joe', "age": 17}}}}

Increment operator

Field in a record can be increased with the $inc operator as part of a PUT request.

PUT https://<dbname>.restdb.io/rest/employee/ID
...
{"$inc": {"age":"1"}, "$inc": {"salary":"5000"}}
...

The increment value must be a part of the request body document. The value to increment must be an integer. Use negative values to decrement field.

Date operator

Querying based on dates are done using the $date operator combined with ISO date strings. For example:

{"_changed":{"$gt":{"$date":"2016-08-01"},"$lt":{"$date":"2016-08-05"}}}

Parameters overview

As part of the REST API, restdb.io has a number of parameters that we can use. They are added to the REST URLs as query parameters (shown in bold below).

https://<db-name>.restdb.io/rest/<collection>[.<format>]?q={}&h={}&...
ParameterDescriptionExample
qDatabase query.GET "Karen Johnston, age 39":
q={"firstname":"Karen", "lastname":"Johnson", age: 39}
GET "with age >= 39":
q={"age": {"$gte": 39}}
Predefined variables for dates are available as $currentDate, $currentMonth and $currentYear. See query examples below.
hQuery hints to specify: fields, max, skip and orderby. Aggregation parameters can also be specified as hints, e.g.:
{"$groupby":["age","city"], "$aggregate":["SUM:salary", "AVG:age"]}. Grouping on date fields are supported as:
{"$groupby": ["$DATE.MMMM:datefield"]}.
Use format strings from the moment.js library in $DATE.[<formatstring>]. (Overrides parameters: metafields, max, skip and sort).
Return title, only 10 records, forward to the 5.th item and sort result set by the body field:
h={"$fields":{"title":1},"$max":10,"$skip":5,"$orderby":{"body":1}}
filterPerforms a text search and retrieves all matching documents.filter=johnson
sortSpecifies which field(s) should be used to sort the result. You can add multiple fields by simply adding another sort parameter. Default sort=_idsort=lastname
dirSort direction. Allowed values are 1 (ascending) and -1 (descending). Used together with sort. Multiple dir parameters can be used in conjunction with sort.dir=-1
skipWhere to start in the result set. Used for paging.skip=100
maxMaximum number of records retrieved.max=20
metafieldsDisplays internal restdb.io fields (_keywords, _tags, _version, _created, _createdby, _mock).metafields=true
groupbyGroup output from query by a field.groupby=gender
aggregatePerform aggregation function on data groups. Useful together with groupby parameter. Avaliable functions; SUM, AVG, MIN, MAX and COUNT.groupby=gender&aggregate=SUM:weight&aggregate=AVG:age
formatOutput format from request. .html returns a standard markup for all fields. .js (Javascript) returns a script that can be included in a web page for search engine indexing. .json standard JSON format..html, .js, .json
apikeyA valid apikey, full access key or a CORS enabled key&apikey=4321fd234f0876....
Not recommended for production. Use header field x-apikey instead

Query examples

The following table shows some examples on how to query restdb.io:

QuerySyntax
Get items that have status = "pro" and title="yay"q={"status”:"pro","title”:”yay"}
Get items that have status = "pro" or "basic"q={"$or":[{"status”:"pro"},{“status”:”basic"}]}
Get items that have status like "basic" (regular expression)q={"status":{"$regex”: ”basic"}}
Get all items updated within date interval (use ISOString or dates that can be converted to ISOString)q={"_changed":{"$gt":{"$date":"2016-08-01"},"$lt":{"$date":"2016-08-05"}}}
Get items created this month, i.e. newer than 1.st 00:00 AM this monthq={"_created":{"$gt":{"$date":"$currentMonth"}}}
Get all items with a score > 900, and group them by categoryh={"$groupby":["category"]}&q={"score":{"$gt": 900}}
Get all metadata for a databasehttps://customerdb-fe0f.restdb.io/rest/_meta
Get an image thumbnail from the media archive<img src="https://rdb-simpledb.restdb.io/media/560263607f98025500000000?s=t">

SQL to NoSQL mapping

The following table shows how traditional SQL statements are expressed as RestDB Queries.

SQLRestDB
SELECT * FROM usershttps://<db-name>.restdb.io/rest/users?q={}
SELECT id, user_id, status FROM users/rest/users?q={}&h={"$fields": {"user_id": 1, "status": 1} }
SELECT * FROM users WHERE status = "A"/rest/users?q={ "status": "A" }
SELECT * FROM users WHERE status != "A"/rest/users?q={"status":{"$not":"A"}}
SELECT * FROM users WHERE status = "A" AND age = 50/rest/users?q={ "status": "A", "age": 50 }
SELECT * FROM users WHERE status = "A" OR age = 50/rest/users?q={ "$or": [ { "status": "A" } ,{ "age": 50 } ] }
SELECT * FROM users WHERE age > 25/rest/users?q={ "age": { "$gt": 25 } }
SELECT * FROM users WHERE age < 25/rest/users?q={ "age": { "$lt": 25 } }
SELECT * FROM users WHERE age > 25 AND age <= 50/rest/users?q={ "age": { "$gt": 25, "$lte": 50 } }
SELECT * FROM users WHERE user_id like "%bc%"/rest/users?q={ "user_id": {"$regex" :"bc"}}
SELECT * FROM users WHERE user_id like "bc%"/rest/users?q={ "user_id": {"$regex" :"^bc"}}
SELECT * FROM users WHERE status = "A" ORDER BY user_id ASC/rest/users?q={ "status": "A" }&sort=user_id&dir=1
SELECT * FROM users WHERE status = "A" ORDER BY user_id DESC/rest/users?q={ "status": "A" }&sort=user_id&dir=-1
SELECT COUNT(*) FROM users/rest/users?q={}&h={"$aggregate":["COUNT:"]}
SELECT COUNT(*) FROM users WHERE age > 30/rest/users?q={"age":{"$gt": 30}}&h={"$aggregate":["COUNT:"]}
SELECT * FROM users LIMIT 1/rest/users?q={}&max=1
SELECT * FROM users LIMIT 5 SKIP 10/rest/users?q={}&max=5&skip=10