Skip to content
This page View Markdown Open in ChatGPT Open in Claude

Query for get data

The read API for queries too long or too private for a URL : the same find, select, sort, skip, limit, deep and getTotalCount as get all, in the body.
Method POST
URL /api/schema/admin/mysql8/inventory/customers/query
Body { find, select, sort, skip, limit, deep, getTotalCount }
Query params none
Answer data : an array of rows, totalCount with getTotalCount: true
Databases MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, TiDB, Percona
Cached yes, with enableCaching on the table
From code g.sys.db.query
API id SCHEMA_POST_QUERY (groups, settings, hooks, WebSocket subscriptions)
Without a schema /api/gen/admin/mysql8/inventory/customers/query : below

Generated (/api/gen) or schema (/api/schema) ?

Both families offer the same operations on the same URL pattern. The schema APIs read the schema of the table : values are converted to their types, validated, encrypted, defaulted and numbered before they reach the database. The generated APIs are schemaless : they pass your data as it is, so a number sent as "22" stays a string. Use the schema APIs for the tables your application writes to, and the generated APIs for quick reads and for tables without a schema.

The URL

/api/schema/admin/mysql8/inventory/customers/query : replace admin with the user path of your account, mysql8 with your instance, inventory with the database and customers with the table. The examples use this table :

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 body

Key Meaning
find Required. The filter, with every operator of find. {} for everything.
select Fields to return : "first_name,last_name", "-password", or { first_name: 1 }.
sort "-last_update,first_name" or { last_update: -1 }.
skip, limit Paging.
deep Related rows, see deep.
getTotalCount true adds totalCount, the number of rows matching find before paging. 
POST /api/schema/admin/mysql8/inventory/customers/query
Body
{
    "find": { "isActive": 1, "pincode": { "$gte": 382345 } },
    "select": "customer_id,first_name,last_name",
    "sort": "-customer_id",
    "skip": 0,
    "limit": 10,
    "getTotalCount": true
}
Answer
{ "success": true, "statusCode": 200, "data": [ { "customer_id": 5, "first_name": "Eve", "last_name": "Page" } ], "totalCount": 5 }

Operators

find takes every query operator of MongoDB, and API Maker translates them to SQL where a table can answer them.

Operator Example Meaning
$eq, $ne { "first_name": { "$ne": "Bob" } } equal, not equal (a plain value means $eq, null asks for the rows without a value)
$gt, $gte, $lt, $lte { "pincode": { "$gte": 382345, "$lt": 382349 } } comparisons on numbers, strings and ISO dates
$in, $nin { "customer_id": { "$in": [ 1, 2 ] } } in a list, not in a list (an array)
$and, $or, $nor { "$or": [ { "customer_id": 1 }, { "pincode": 382345 } ] } every, any, none of the conditions (an array), inside each other as deep as needed
$not { "customer_id": { "$not": { "$gt": 3 } } } negate the operators
$exists { "last_update": { "$exists": false } } with or without the field (IS NULL on SQL)
$regex, $options { "first_name": { "$regex": "^ma", "$options": "i" } } regular expression
$mod { "customer_id": { "$mod": [ 2, 0 ] } } remainder of a division
$expr { "$expr": { "$gt": [ "$spent", "$budget" ] } } compare fields with each other
$like { "first_name": { "$like": "Ma%" } } SQL like pattern, % and _
$isNull { "last_update": { "$isNull": true } } null (or not null with false), SQL databases
  • Several operators on one field are all required, on every database : { "pincode": { "$gte": 382345, "$lt": 382349 } }.
  • $type, $all, $elemMatch, $size, $text, $where, $jsonSchema and the geospatial operators are for MongoDB instances ; the bitwise operators work on every database.
  • See find for every operator, what each one becomes in SQL, and how null and regular expressions behave per database.

Find and join

A dotted key in find filters on a field of a related table, across databases and instances :

Orders of gold customers : orders.customer_id refers to customers
{ "find": { "customer_id.loyalty_tier": "GOLD" } }
  • The relation comes from the schema of the table, as many levels as the dots : "customer_id.city_id.name": "Pune". A deep item of the call does not make one, and a virtual field can not be used.
  • The rows of the related table are read through their own query API, with the permissions of the caller.
  • See find and join for every rule.

Deep populate

{
    "find": {},
    "limit": 5,
    "deep": [ {
        "s_key": "customer_id", "t_col": "orders", "t_key": "customer_id", "isMultiple": true, "select": "order_no,total",
        "deep": [ { "s_key": "shipping_id", "t_col": "shippings", "t_key": "id" } ]
    } ]
}
  • Two items in deep populate two fields ; deep inside an item goes one level further. With relations in the schema, { "s_key": "customer_id" } is enough.

Types in filters

  • The values of find are converted to the types of the schema before the query : "customer_id": "8" matches the number 8.

Caching

  • With caching on for the table, the same body answers from Redis until a write changes the table. x-am-cache-control: reset_cache forces the database.

From your code

g.sys.db.query makes the same call from custom APIs, hooks, events, schedulers and test cases, with the same headers and params. To get the whole response envelope instead of data, pass true as the second argument in TypeScript, or call it through g.sys.db.full in the other languages.

Without a schema : /api/gen

Every table also answers this operation under /api/gen, with or without a schema :
URL /api/gen/admin/mysql8/inventory/customers/query
API id GEN_POST_QUERY (groups, settings, hooks, WebSocket subscriptions)
From code g.sys.db.gen.queryGen

The data passes as it is : nothing is converted, validated, defaulted, encrypted or numbered, and a filter matches the type you send ("22" does not match the number 22). On MongoDB, strings which look like ObjectIds are converted. The query params, headers and errors are the ones of this page.

Headers

Every call takes the request headers : the tokens, x-am-response-case, x-am-content-type-response (JSON, XML, YAML…), x-am-response-object-type: make_flat, x-am-cache-control, x-am-get-encrypted-data, x-am-internationalization, x-am-tenant-username, x-am-meta.

Errors

Code When
401 The token in x-am-authorization is missing or invalid.
403 No group of the API user grants this API of this table, or a field of the request.
404 The instance, database or table of the URL does not exist, or the table has no schema (use /api/gen).
400 The query or the body is wrong : the error messages say which key and why.