Get started with your own database.

Querying with the API

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


Parameters overview

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 item and sort result set by the body field:
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 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 or CORS enabled&apikey=4321fd234f0876...

SQL to NoSQL mapping

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

SELECT * FROM usershttps://<db-name>{}
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

Query examples

The following table shows some examples on how to query

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 "pro" (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 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 database
Get an image thumbnail from the media archive<img src="">