Query parameters¶
The read APIs of a table (get all, get all by stream, get by id, distinct) take their options as query params of the URL. The query APIs take the same keys in their JSON body, and the write APIs accept select and deep to shape their answer.
| customer_id | first_name | last_name | last_update | pincode | isActive |
|---|---|---|---|---|---|
| 1 | Bob | lin | 2022-11-14 04: 34: 58 | 382345 | 1 |
| 2 | Alice | Page | 2022-10-15 02: 10: 40 | 382346 | 1 |
| 3 | Mallory | Brown | 2022-09-13 03: 44: 05 | 382347 | 1 |
| 4 | Eve | Mathly | 2022-11-12 01: 59: 33 | 382348 | 1 |
| 5 | Eve | Page | 2022-11-12 01: 59: 33 | 382349 | 1 |
The params¶
| Param | Example | What it does |
|---|---|---|
| find | ?find={first_name:'Bob',pincode:{$gte:382345}} |
Filter with the MongoDB operators, on every database, including fields of related tables. |
| any column | ?first_name=Bob,Alice&pincode>=382345 |
A column name is a filter too, with >, >=, <, <=, !=, comma lists and regular expressions. |
| skip | ?skip=20 |
Leave out the first rows. |
| limit | ?limit=10 |
At most this many rows. Without it, every row. |
| sort | ?sort=-last_update,first_name |
Order, - for descending, several columns. |
| select | ?select=first_name,last_name or ?select=-password |
Only these fields, or everything but these. |
| deep | ?deep=[{s_key:'customer_id',t_col:'orders',t_key:'customer_id',isMultiple:true}] |
Replace ids with the related rows, from any table, database or instance, any depth. |
| getTotalCount | ?getTotalCount=true |
Add totalCount : the rows matching the filter before paging. |
| upsert | ?upsert=true |
Update by id and replace by id : insert when the id does not exist. |
| returnDocument | ?returnDocument=before |
Update by id and replace by id : answer with the row before the change. |
| throwErrorIfRecordNotFound | ?throwErrorIfRecordNotFound=true |
Update, replace and remove by id : 404 instead of data: null for an unknown id. |
skip¶
Leaves out the first rows. With limit, it pages through a table : page 3 of 10 rows is skip=20&limit=10.
- A whole number ;
0or absent skips nothing. - Always add a
sortwhen you page : without an order, the database may return the rows in any order, and a page may repeat or miss rows.
limit¶
At most this many rows. Without it, get all and the query APIs return every row which matches : give a limit as soon as a table can grow.
{
"success": true,
"statusCode": 200,
"data": [
{ "customer_id": 1, "first_name": "Bob", "last_name": "Lin" },
{ "customer_id": 2, "first_name": "Alice", "last_name": "Page" }
]
}
- A whole number greater than zero.
- The streams (get all by stream, query by stream) send big results without a limit and without holding them in memory.
sort¶
Orders the rows : name the columns, with - in front of a column for descending order.
{ "find": {}, "sort": "-last_update,first_name" }
{ "find": {}, "sort": { "last_update": -1, "first_name": 1 } }
- Spaces around the commas are fine. On MongoDB a nested field sorts too :
sort=address.city. - Sorting a big table on a column without an index is slow : Index Maker finds the queries which need one.
getTotalCount¶
getTotalCount=true adds totalCount to the answer : the rows matching the filter, before skip and limit. One call gives the page and the total.
GET /api/schema/admin/mysql8/inventory/customers?find={isActive:1}&skip=0&limit=2&getTotalCount=true
{
"success": true,
"statusCode": 200,
"data": [
{ "customer_id": 1, "first_name": "Bob", "last_name": "Lin" },
{ "customer_id": 2, "first_name": "Alice", "last_name": "Page" }
],
"totalCount": 5
}
- It works on get all, get all by stream, query and query by stream ; a stream sends
totalCountafter the rows. The count API gives the number alone. - The count is a second query on the database : on a very big table with a complex filter, it costs as much as the query itself.
upsert¶
upsert=true makes update by id and replace by id insert a row when the id does not exist. Without it, an unknown id changes nothing and answers data: null.
| Row 999 | What happens | Answer |
|---|---|---|
| exists | updated with the body | the row after the change |
| does not exist | inserted with customer_id: 999 and the body |
the new row |
- The inserted row is validated as a save : with a schema, every
requiredfield must be in the body. - From code :
g.sys.db.updateById({ ..., upsert: true }).
returnDocument¶
Which version of the row an update by id or a replace by id sends back : after the change (the default), or before it. It changes the answer only, never what is written.
{
"success": true,
"statusCode": 200,
"data": { "customer_id": 1, "first_name": "Bob", "last_name": "Lin", "pincode": 382345 }
}
selectanddeepapply to the version returned. Withupsertand an insert,beforeanswersnull.- From code :
g.sys.db.updateById({ ..., returnDocument: 'before' }).
In a deep item¶
skip, limit and sort inside a deep item apply to the related rows of each parent : the 5 latest orders of every customer, for example.
Writing them¶
- Values are JSON5 :
find={first_name:'Bob'}needs no quotes around keys and accepts single quotes. Strict JSON works too. - URL encode the value when your client does not :
find=%7Bfirst_name%3A'Bob'%7D. The API testing page andg.sys.dbdo it for you. - Numbers, booleans and ISO dates in a column param are cast to their type before the query (
?pincode=382345compares a number). - The same keys go in the body of the query APIs, as JSON :
{ "find": { "first_name": "Bob" }, "sort": "-customer_id", "skip": 0, "limit": 10, "select": "first_name,last_name", "deep": [ { "s_key": "customer_id", "t_col": "orders", "t_key": "customer_id" } ], "getTotalCount": true }
From code¶
g.sys.db.getAll({ instance, database, collection, queryParams: { find: {…}, sort: '-customer_id', limit: 10 } }) : queryParams takes the same keys, as objects rather than strings. See the code examples.
Schema or generated¶
The schema APIs convert the values of find to the types of the schema ("8" matches the number 8) and know the relations for deep. The generated APIs compare what you send as it is. See All APIs at a glance.