This page View Markdown Open in ChatGPT Open in Claude

Query for get data¶

The read API for a query that is too long or too private for a URL. It takes the keys of get all, in a JSON body.

In short

POST /api/schema/<user-path>/<instance>/<database>/<table>/query
Body : { "find": {…}, "sort": …, "skip": …, "limit": …, "select": …, "deep": […], "getTotalCount": true }
find is required. {} matches every row.
Cached like get all, when caching is on.


Method	POST
URL	`/api/schema/admin/mysql8/inventory/customers/query`
Body	`{ find, select, sort, skip, limit, deep, getTotalCount }`
Query params	none
Answer	`data` : a list of rows. `totalCount` with `getTotalCount: true`.
Databases	MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, TiDB, Percona
Cached	yes, when `enableCaching` is on for the table or the database
From code	`g.sys.db.query`
API id	`SCHEMA_POST_QUERY`
Without a schema	`/api/gen/admin/mysql8/inventory/customers/query` : below

The examples read the table of get all.

The body¶

Key	What it holds
`find`	Required. The filter, with every operator of find. `{}` for every row.
`select`	The columns : `"first_name,last_name"`, `"-password"`, or `{ "first_name": 1 }`. See select.
`sort`	`"-last_update,first_name"`, or `{ "last_update": -1 }`.
`skip`, `limit`	Pages. See skip and limit.
`deep`	Related rows. See deep.
`getTotalCount`	`true` adds `totalCount` : how many rows match `find`, before `skip` and `limit`.

Any other key is refused : Only ['find', 'sort', 'select', 'skip', 'limit', 'deep', …] properties are allowed.

POST /api/schema/admin/mysql8/inventory/customers/query

Body

{
    "find": { "isActive": 1, "pincode": { "$gte": 382346 } },
    "select": "first_name,last_name",
    "sort": "-customer_id",
    "limit": 2,
    "getTotalCount": true
}

Answer

{
    "success": true,
    "statusCode": 200,
    "data": [
        { "customer_id": 5, "first_name": "Eve", "last_name": "Page" },
        { "customer_id": 4, "first_name": "Eve", "last_name": "Mathly" }
    ],
    "totalCount": 4
}

A key with dots in find reaches a column of a related table, also in another database or instance :

The orders of gold customers : orders.customer_id refers to customers

{ "find": { "customer_id.loyalty_tier": "GOLD" } }

The relation comes from the schema. Every rule is on find and join.

Body : 5 customers, each with their orders, each order with its shipping

{
    "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" } ]
    } ]
}

With relations in the schema, { "s_key": "customer_id" } is enough. See deep.

Good to know¶

Types. The values of find take the types of the schema : "customer_id": "8" matches the number 8.
Cache. With caching on, the same body answers from Redis until a write changes the table. The hooks do not run for a cached answer. x-am-cache-control: reset_cache reads the database again.
Hooks. A pre hook can narrow g.req.body.find, for example to the rows of the person who calls.
Many rows. For a big result, use query for get data by stream.

From your code¶

g.sys.db.query makes the same call from custom APIs, hooks, events, schedulers and test cases.

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`
From code	`g.sys.db.gen.queryGen`

find matches the type you send : "22" does not match the number 22. On MongoDB, a text that looks like an ObjectId is turned into one. A key with dots is not a join here : it goes to the database as it is.

Headers and errors¶

This operation takes the request headers of every API. It answers the envelope, with the status codes and the messages of every API.

Related : All APIs · Table schema · Pre hooks · Post hooks · API group permission