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. In this section we will give you plenty of REST API examples using MongoDB like query syntax.
🚀 If you love the ease of making APIs and mongodb-like queries of restdb.io, please also check out our new backend service: codehooks.io. It integrates Javascript serverless functions with a document database (with MongoDB-like queries of course), a key/value database, a message queue and a CRON-like job system. A powerful CLI lets you instantly deploy new code.
Database queries are created as valid JSON documents. A query object consists of fields and operators that make up a complete query. Please note that query fields are case sensitive.
A simple query example:
https://mydb-fafc.restdb.io/rest/customers?q={"status": "GOLD"}
Which is the same as asking the database: fetch all records with status GOLD
Please note that maximum 1000 records are returns from any query unless the $max parameter is specified.
Match a single field (find Joe):
https://mydb-fafc.restdb.io/rest/people?q={"name": "Joe"}
Match multiple fields (find Joe who's 17):
https://mydb-fafc.restdb.io/rest/people?q={"name": "Joe", "age": 17}
In addition to query simple fields, you can use special operators to perform more advanced queries.
Return documents not matching the query.
https://mydb-fafc.restdb.io/rest/people?q={"name" : {"$not" : "Joe"}}
Return documents matching any values.
https://mydb-fafc.restdb.io/rest/people?q={"name" : {"$in" : ["Joe", "Jane", "Donald"]}}
Return documents not matching any of the values.
https://mydb-fafc.restdb.io/rest/people?q={"name" : {"$nin" : ["Joe", "Jane", "Donald"]}}
Match a regular expression against field.
https://mydb-fafc.restdb.io/rest/people?q={"name" : {"$regex" : ".Joe*"}}
Return documents that matches one or the other field.
https://mydb-fafc.restdb.io/rest/people?q={"$or": [{"name": "Jane"}, {"name": "Donald"}]}
Return documents both fields.
https://mydb-fafc.restdb.io/rest/people?q={"$and": [{"name": "Jane"}, {"last-name": "Cassidy"}]}
| Operator | Description | Example |
|---|---|---|
| $not | Negation logical operator | {"field" : {"$not" : val}} |
| $in | Match any value in array | {"field" : {"$in" : [value1, value2, ...]}} |
| $nin | Not match any value in array | {"field" : {"$nin" : [value1, value2, ...]}} |
| $regex | Match field | {"field" : {"$regex" : ".*"}} |
| $or | Logical operator | {"$or": [{"status": "GOLD"}, {"status": "SILVER"}]} |
| $and | Logical operator | {"$and": [{"status": "GOLD"}, {"sales": 1000}]} |
Return documents that matches each field value greater than numeric value.
https://mydb-fafc.restdb.io/rest/people?q={"salary": {"$gt": 10000}}
Return documents that matches each field value greater than or equal to numeric value.
https://mydb-fafc.restdb.io/rest/people?q={"salary": {"$gte": 10000}}
Return documents that matches each field value less than numeric value.
https://mydb-fafc.restdb.io/rest/people?q={"salary": {"$lt": 10000}}
Return documents that matches each field value less than or equal to numeric value.
https://mydb-fafc.restdb.io/rest/people?q={"salary": {"$lte": 10000}}
Return documents that matches each field value between two numeric values.
https://mydb-fafc.restdb.io/rest/people?q={"salary": {"$bt": [5000, 7500]}}
Return documents that matches each field with a value.
https://mydb-fafc.restdb.io/rest/people?q={"field": {"$exists": true}}
Return documents that matches each sub field with any value.
https://mydb-fafc.restdb.io/rest/people?q={"field.0": {"$exists": true}}
Return documents that matches at least one of the elements in an array field.
https://mydb-fafc.restdb.io/rest/people?q={"contact":{"$elemMatch":{"name":"Anderson", age:35}}}
Return array of unique values from matching fields.
https://mydb-fafc.restdb.io/rest/people?q={"$distinct": "age"}&sort=age
Example output:
[
"18",
"19",
"20",
"21",
"22",
"23",
"24",
"25",
"26"
]
| Operator | Description | Example |
|---|---|---|
| $gt | > | {"salary": {"$gt": 10000}} |
| $gte | >= | {"salary": {"$gte": 10000}} |
| $lt | < | {"salary": {"$lt": 10000}} |
| $lte | <= | {"salary": {"$lte": 10000}} |
| $bt | >= value <= | {"salary": {"$bt": [5000, 7500]}} |
| $exists | Check if field exists | {"field": {"$exists": true|false}} |
| $exists (array) | Check if array field exists or is empty | {"field.0": {"$exists": true|false}} |
| $elemMatch | Array element matching | {"contact":{"$elemMatch":{"name":"Anderson", age:35}}} |
| $distinct | Array with unique element values | {"$distinct": "name"} |
Querying based on dates are done using the $date operator combined with ISO date strings.
For example:
// between two dates
{"_changed":{"$gt":{"$date":"2016-08-01"},"$lt":{"$date":"2016-08-05"}}}
To enable more dynamic queries using the $date operator, like "get all records this month", we have added a number of predefined date variables.
// older than the time just now
{"startat":{"$lt":{"$date":"$now"}}}
| Name | Description |
|---|---|
| $now | just now |
| $currentMinute | start of last minute |
| $currentHour | start of last hour |
| $currentDate | start of current day |
| $today | start of current day |
| $tomorrow | start of next day |
| $yesterday | start of day before |
| $currentWeek | start of this week (sunday) |
| $currentMonth | start of this month |
| $currentYear | start of this year |
| $nextWeek | start of next week (sunday) |
| $nextMonth | start of next month |
| $nextYear | start of next year |
We also support MomentJS. Check out their docs and compose the date variable like this:
{"startat":{"$gt":{"$date":"$moment.endOf.hour"}}}
{"startat":{"$gt":{"$date":"$moment.subtract.3.days"}}}
{"startat":{"$gt":{"$date":"$moment.subtract.3.hours"},"$lt":{"$date":"$moment.add.3.hours"}}}
{"startat":{"$gt":{"$date":"$moment.startOf.year"},"$lt":{"$date":"$moment.endOf.year"}}}
The $orderbyoperator lets you sort the result from a query. You can sort on single or multiple fields within the same query.
We use the URL parameter hint h={..} to specify sorting.
E.g. query all records from peopleand sort the result set by name in ascending order, and then by age in descending order.
https://mydb-fafc.restdb.io/rest/people?q={}&h={"$orderby": {"name": 1, "age": -1}}
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 below:
https://<db-name>.restdb.io/rest/<collection>[.<format>]?q={}&h={}&...
The following table shows all valid URL parameters to a database REST endpoint:
| Parameter | Description |
|---|---|
| q | Database query |
Example: GET "Karen Johnston, age 39":https://mydb-fafc.restdb.io/rest/people?q={"firstname":"Karen", "lastname":"Johnson", age: 39} | |
| h | Query hints to specify: fields, max, skip and orderby. Aggregation parameters can also be specified as hints, read more here |
Example: Return title, only 10 records, forward to the 5.th item and sort result set by the body field:https://mydb-fafc.restdb.io/rest/people?q={}&h={"$fields":{"title":1},"$max":10,"$skip":5,"$orderby":{"body":1}} | |
| filter | Performs a text search and retrieves all matching documents |
Example: https://mydb-fafc.restdb.io/rest/people?q={}&filter=johnson | |
| sort | Specifies which field(s) should be used to sort the result. You can add multiple fields by simply adding another sort parameter. Default sort=_id |
Example: https://mydb-fafc.restdb.io/rest/people?q={}&sort=lastname | |
| dir | Sort direction. Allowed values are 1 (ascending) and -1 (descending). Used together with sort. Multiple dir parameters can be used in conjunction with sort. |
Example: https://mydb-fafc.restdb.io/rest/people?q={}&dir=-1 | |
| skip | Where to start in the result set. Used for paging. |
Example: https://mydb-fafc.restdb.io/rest/people?skip=100 | |
| max | Maximum number of records retrieved. Default is 1000. |
Example: https://mydb-fafc.restdb.io/rest/people?max=20 | |
| totals | &totals=true returns an object with both data and total count. Totals equals to max parameter or default 1000 |
Example: output from query ->{data: [ … ], totals: { total: 100, count: 40, skip: 0, max: 1000}} | |
| totals and count | &totals=true&count=true returns an object with no data and just the total count |
Example: output from query -> {data: [], totals: { count: 42}} | |
| metafields | Displays internal restdb.io fields (_keywords, _tags, _version, _created, _createdby, _mock) |
Example: https://mydb-fafc.restdb.io/rest/people?metafields=true | |
| groupby | Group output from query by a field |
Example: https://mydb-fafc.restdb.io/rest/people?groupby=gender | |
| aggregate | Perform aggregation function on data groups. Useful together with groupby parameter. Avaliable functions; SUM, AVG, MIN, MAX and COUNT Docs |
Example: https://mydb-fafc.restdb.io/rest/people?groupby=gender&aggregate=SUM:weight&aggregate=AVG:age | |
| format | Output 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 |
Example: https://mydb-fafc.restdb.io/rest/people.html | |
| apikey | A valid apikey, full access key or a CORS enabled key |
Example: https://mydb-fafc.restdb.io/rest/people?&apikey=4321fd234f0876.... Not recommended for production. Use header field x-apikey instead | |
| idtolink | Inserts canonical URLs for image references and object references |
Example: https://mydb-fafc.restdb.io/rest/people?idtolink=true | |
| flatten | Used together with idtolink. Extract links as properties on root object |
Example: https://mydb-fafc.restdb.io/rest/people?flatten=true | |
| referencedby | Return all items that refers to a record. Requires a record _id in the query or path |
Example: https://mydb-fafc.restdb.io/rest/people/56011150e1321c7300000001?referencedby=true | |
| fetchmediadata | Replace media ID's with a full record from the media archive |
Example: https://mydb-fafc.restdb.io/rest/people?fetchmediadata=true | |
| fetchchildren | Insert records from parent-child relation on parent record |
Example: https://mydb-fafc.restdb.io/rest/company?fetchchildren=true |
The following table shows some examples on how to query restdb.io:
| Query | Syntax |
|---|---|
| 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 month | q={"_created":{"$gt":{"$date":"$currentMonth"}}} |
| Get all items with a score > 900, and group them by category | h={"$groupby":["category"]}&q={"score":{"$gt": 900}} |
| Get all metadata for a database | https://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"> |
Comprehensive code examples for various languages
The following table shows how traditional SQL statements are expressed as RestDB Queries.
| SQL | RestDB |
|---|---|
| SELECT * FROM users | https://<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 |
