Distinct with query¶

Distinct, with the filter in a JSON body : the different values of a column, among the rows that find matches.

In short

POST /api/schema/<user-path>/<instance>/<database>/<table>/distinct/<column>[/<order>]
Body : { "find": {…} }. { "find": {} } lists every value.
Columns and orders in the URL work as in distinct.


Method	POST
URL	`/api/schema/admin/mysql8/inventory/customers/distinct/:field[/:order]`
Body	`{ find }`
Query params	none
Answer	`data` : a list of objects, one for each value or combination
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.distinctQuery`
API id	`SCHEMA_POST_DISTINCT_QUERY`
Without a schema	`/api/gen/admin/mysql8/inventory/customers/distinct/:field[/:order]` : below

The examples read the table of get all.

The call¶

POST /api/schema/admin/mysql8/inventory/customers/distinct/customer_id/asc

Body

{ "find": { "pincode": { "$in": [ 382345, 382346 ] } } }

Answer

{ "success": true, "statusCode": 200, "data": [ { "customer_id": 1 }, { "customer_id": 2 } ] }

find takes every operator of find : { "first_name": { "$like": "%llory" } }.
The values take the types of the schema : "pincode": "382345" matches the number.
The columns of find must be ones the groups of the caller may read.

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

Every table also answers this operation under /api/gen, with or without a schema :


URL	`/api/gen/admin/mysql8/inventory/customers/distinct/:field[/:order]`
API id	`GEN_POST_DISTINCT_QUERY`
From code	`g.sys.db.gen.distinctQueryGen`

find matches the type you send. On MongoDB, a text that looks like an ObjectId is turned into one.

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