# Query for Get Data API (generated)

> Read rows with a JSON query in the body with the generated query API of API Maker - find with operators, find and join across tables, select, sort, paging, deep populate and total count.

Source: https://docs.apimaker.dev/v1/docs/apis-all/generated-apis/auto-generated-query-for-get-data-api.html

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/gen/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.gen.queryGen`](https://docs.apimaker.dev/v1/examples/sys/db/gen/queryGen.html) |
| API id | `GEN_POST_QUERY` (groups, settings, hooks, WebSocket subscriptions) |
| The other family | [Query for get data as a schema API](https://docs.apimaker.dev/v1/docs/apis-all/schema-apis/auto-generated-schema-based-query-for-get-data-api.html) |

> **Schemaless : the data passes as it is**
>
> The generated APIs never read the [schema](https://docs.apimaker.dev/v1/docs/schema/schema.html) of the table. Nothing is converted, validated, defaulted, encrypted or numbered : what you send is what the database gets, and a filter matches the type you send (`"22"` does not match the number `22`). On MongoDB, strings which look like ObjectIds are converted. Every table has these APIs, with or without a schema ; the same operations under `/api/schema` apply the schema.

## The URL

`/api/gen/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](https://docs.apimaker.dev/v1/docs/apis-all/query-params/find.html). `{}` 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](https://docs.apimaker.dev/v1/docs/apis-all/query-params/deep.html). |
| `getTotalCount` | `true` adds `totalCount`, the number of rows matching `find` before paging. |

```text
POST /api/gen/admin/mysql8/inventory/customers/query
```

**Body**

```json
{
    "find": { "isActive": 1, "pincode": { "$gte": 382345 } },
    "select": "customer_id,first_name,last_name",
    "sort": "-customer_id",
    "skip": 0,
    "limit": 10,
    "getTotalCount": true
}
```

**Answer**

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

## Operators

Every operator works on every database ; API Maker translates them to SQL where needed.

| Operator | Example | Meaning |
|---|---|---|
| `$eq`, `$ne` | `{ "first_name": { "$ne": "Bob" } }` | equal, not equal (a plain value means `$eq`) |
| `$gt`, `$gte`, `$lt`, `$lte` | `{ "pincode": { "$gte": 382345 } }` | comparisons on numbers, strings and ISO dates |
| `$in`, `$nin` | `{ "customer_id": { "$in": [ 1, 2 ] } }` | in a list, not in a list (a non empty array) |
| `$and`, `$or` | `{ "$or": [ { "customer_id": 1 }, { "pincode": 382345 } ] }` | combine conditions (an array) |
| `$not` | `{ "customer_id": { "$not": { "$in": [ 1, 2 ] } } }` | negate an operator |
| `$like` | `{ "first_name": { "$like": "Ma%" } }` | SQL like pattern, `%` and `_` |
| `$regex` | `{ "first_name": { "$regex": "^Ma" } }` | regular expression |
| `$isNull` | `{ "last_update": { "$isNull": true } }` | null (or not null with `false`), SQL databases |

- On SQL databases, two operators on one field (`{ "$gte": 1, "$lte": 9 }`) are refused : write them as two conditions in `$and`.

## Find and join

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

**Customers whose orders have status PAID**

```json
{ "find": { "orders.status": "PAID" }, "deep": [ { "s_key": "customer_id", "t_col": "orders", "t_key": "customer_id", "isMultiple": true } ] }
```

- The relation comes from the `deep` item of the call, or from the schema of the table. It goes as many levels as the dots : `"orders.items.product.category": "Books"`.
- The rows of the related table are read through their own query API, with the permissions of the caller.
- See [find and join](https://docs.apimaker.dev/v1/docs/apis-all/query-params/find.html#find-and-join-fields-of-related-tables) for every rule.

## Deep populate

```json
{
    "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

- Nothing is converted : `"customer_id": "8"` looks for the string `"8"` and matches no number. Send the type the column has.

## 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.gen.queryGen`](https://docs.apimaker.dev/v1/examples/sys/db/gen/queryGen.html) makes the same call from custom APIs, hooks, events, schedulers and test cases, with the same headers and params. Pass `true` as the second argument to get the whole [response envelope](https://docs.apimaker.dev/v1/docs/apis-all/response-format.html) instead of `data`.

## Headers

Every call takes the [request headers](https://docs.apimaker.dev/v1/docs/apis-all/header/requestHeader.html) : 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. |
| `400` | The query or the body is wrong : the [error messages](https://docs.apimaker.dev/v1/docs/apis-all/error-codes.html) say which key and why. |

## Related

- [All APIs at a glance](https://docs.apimaker.dev/v1/docs/apis-all/overview.html) · [Query params](https://docs.apimaker.dev/v1/docs/apis-all/query-params/query-params.html) · [Response format](https://docs.apimaker.dev/v1/docs/apis-all/response-format.html) · [Pre hooks](https://docs.apimaker.dev/v1/docs/apis-all/hooks/preHook-api.html) and [post hooks](https://docs.apimaker.dev/v1/docs/apis-all/hooks/postHook-api.html) · [Automatic caching](https://docs.apimaker.dev/v1/docs/features/automatic-caching.html)