# find Parameter

> Filter rows with the find parameter of API Maker - every query operator of MongoDB ($eq, $in, $and, $or, $nor, $not, $exists, $regex, $mod, $expr, $elemMatch...), translated to SQL for MySQL, MariaDB, PostgreSQL, SQL Server and Oracle, and find and join on fields of related tables.

Source: https://docs.apimaker.dev/v4/docs/apis-all/query-params/find.html

`find` filters the rows. It takes an object written as MongoDB writes a filter, with every query operator of MongoDB, and API Maker translates it for MySQL, MariaDB, PostgreSQL, SQL Server and Oracle as well, so one filter language serves every database.

**As a query param (JSON5)**

```text
GET /api/schema/admin/mysql8/inventory/customers?find={first_name:'Bob'}
```

**In the body of the query APIs**

```json
{ "find": { "first_name": "Bob" } }
```

| 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        |

## Operators

On a MongoDB instance every operator is the one of MongoDB. The last column says what a SQL instance is asked : **MongoDB** there means that no table answers it, and API Maker refuses it on a SQL instance with `Unknown Operator`.

### Comparison

| Operator | Example | Rows returned | SQL |
|---|---|---|---|
| a value | `{ "customer_id": 2 }` | equal to the value | `=` |
| `$eq` | `{ "customer_id": { "$eq": 2 } }` | equal | `=` |
| `$ne` | `{ "customer_id": { "$ne": 2 } }` | not equal | `!=` |
| `$gt`, `$gte` | `{ "customer_id": { "$gt": 2 } }` | greater than, greater or equal | `>`, `>=` |
| `$lt`, `$lte` | `{ "customer_id": { "$lte": 2 } }` | less than, less or equal | `<`, `<=` |
| `$in` | `{ "customer_id": { "$in": [ 1, 2 ] } }` | one of the values | `IN (...)` |
| `$nin` | `{ "customer_id": { "$nin": [ 2, 3, 4 ] } }` | none of the values | `NOT IN (...)` |

- Two keys in one object are both required : `{ "first_name": "Eve", "pincode": 382349 }`.
- Several operators on one field are all required, on every database : `{ "customer_id": { "$gte": 1, "$lte": 9 } }`.
- Comparisons work on numbers, strings and dates in ISO format : `{ "last_update": { "$gte": "2022-11-01T00:00:00.000Z" } }`.
- `$in` and `$nin` take an array. `$in` of an empty array returns no row, `$nin` of one every row.

### Logical

| Operator | Example | Rows returned | SQL |
|---|---|---|---|
| `$and` | `{ "$and": [ { "customer_id": 1 }, { "pincode": 382345 } ] }` | every condition | `a AND b` |
| `$or` | `{ "$or": [ { "customer_id": 1 }, { "pincode": 382345 } ] }` | any condition | `(a OR b)` |
| `$nor` | `{ "$nor": [ { "customer_id": 1 }, { "pincode": 382345 } ] }` | none of the conditions | `NOT (a OR b)` |
| `$not` | `{ "customer_id": { "$not": { "$gt": 3 } } }` | the opposite of the operators | `NOT (a > ?)`, `NOT IN`, `NOT LIKE` |

`$and`, `$or` and `$nor` take an array of conditions, and go inside each other as deep as you need. Columns next to them are required too.

**Columns, $or, $and and $nor together**

```json
{
    "isActive": 1,
    "$or": [
        { "first_name": "Eve", "pincode": { "$gte": 382348 } },
        { "$and": [ { "last_name": "Page" }, { "$or": [ { "customer_id": 2 }, { "customer_id": { "$in": [ 7, 9 ] } } ] } ] }
    ],
    "$nor": [ { "first_name": "Mallory" }, { "pincode": { "$lt": 382345 } } ]
}
```

**What a PostgreSQL instance is asked**

```sql
"isActive" = $1
AND (("first_name" = $2 AND "pincode" >= $3) OR ("last_name" = $4 AND ("customer_id" = $5 OR "customer_id" IN ($6, $7))))
AND NOT ("first_name" = $8 OR "pincode" < $9)
```

### Element

| Operator | Example | Rows returned | SQL |
|---|---|---|---|
| `$exists` | `{ "last_update": { "$exists": true } }` | with the field, or without it (`false`) | `IS NOT NULL`, `IS NULL` |
| `$type` | `{ "pincode": { "$type": "string" } }` | whose value is of a BSON type | MongoDB |

### Evaluation

| Operator | Example | Rows returned | SQL |
|---|---|---|---|
| `$regex`, `$options` | `{ "first_name": { "$regex": "^ma", "$options": "i" } }` | matching the regular expression | [by database](https://docs.apimaker.dev/v4/docs/apis-all/query-params/find.html#regular-expressions) |
| `$mod` | `{ "customer_id": { "$mod": [ 2, 0 ] } }` | whose value divided by 2 leaves 0 | `MOD(a, 2) = 0`, `a % 2 = 0` |
| `$expr` | `{ "$expr": { "$gt": [ "$spent", "$budget" ] } }` | for which the expression is true : it compares fields | [comparisons and arithmetic](https://docs.apimaker.dev/v4/docs/apis-all/query-params/find.html#expr-on-a-sql-instance) |
| `$text` | `{ "$text": { "$search": "coffee" } }` | found by the text index | MongoDB |
| `$where` | `{ "$where": "this.a > this.b" }` | for which the JavaScript is true | MongoDB |
| `$jsonSchema` | `{ "$jsonSchema": { "required": [ "name" ] } }` | valid against the schema | MongoDB |
| `$comment` | `{ "$comment": "nightly report", "isActive": 1 }` | a note next to the conditions, it filters nothing | left out |

### Array

A field of a MongoDB document can hold an array. A column of a SQL table holds one value, so these are for MongoDB instances.

| Operator | Example | Rows returned |
|---|---|---|
| a value | `{ "tags": "red" }` | whose array holds the value |
| an operator | `{ "scores": { "$gt": 80 } }` | whose array holds a value which meets it |
| `$all` | `{ "tags": { "$all": [ "red", "blue" ] } }` | whose array holds every value |
| `$elemMatch` | `{ "orders": { "$elemMatch": { "qty": { "$gt": 5 }, "status": "PAID" } } }` | with one item which meets every condition |
| `$elemMatch` | `{ "scores": { "$elemMatch": { "$gt": 80, "$lt": 90 } } }` | with one value which meets every operator |
| `$size` | `{ "tags": { "$size": 2 } }` | whose array has that many items |
| a position | `{ "orders.0.qty": { "$gt": 5 } }` | whose first item meets the condition |

`$all` takes `$elemMatch` items, and `$not` negates `$elemMatch` : `{ "orders": { "$not": { "$elemMatch": { "status": "PAID" } } } }`.

### Bitwise

| Operator | Example | Rows returned | SQL |
|---|---|---|---|
| `$bitsAllSet` | `{ "flags": { "$bitsAllSet": [ 1, 5 ] } }` | with every one of the bits set | `(a & 34) = 34` |
| `$bitsAnySet` | `{ "flags": { "$bitsAnySet": 34 } }` | with one of the bits set | `(a & 34) <> 0` |
| `$bitsAllClear` | `{ "flags": { "$bitsAllClear": [ 1, 5 ] } }` | with none of the bits set | `(a & 34) = 0` |
| `$bitsAnyClear` | `{ "flags": { "$bitsAnyClear": 34 } }` | with one of the bits not set | `(a & 34) <> 34` |

The bits are a number, or the array of their positions (the first one is 0). Oracle is asked with `BITAND(a, 34)`.

### Geospatial

For MongoDB instances, on a field with a `2dsphere` or `2d` index.

| Operator | Example |
|---|---|
| `$geoWithin`, `$geoIntersects` | `{ "place": { "$geoWithin": { "$centerSphere": [ [ 72.5, 23.0 ], 0.01 ] } } }` |
| `$near`, `$nearSphere` | `{ "place": { "$near": { "$geometry": { "type": "Point", "coordinates": [ 72.5, 23.0 ] }, "$maxDistance": 500 } } }` |

### Of API Maker

| Operator | Example | Rows returned |
|---|---|---|
| `$like` | `{ "first_name": { "$like": "Bob%" } }` | SQL pattern, `%` any text, `_` one character, on every database |
| `$isNull` | `{ "last_update": { "$isNull": true } }` | null, or not null with `false` (SQL databases) |

`$like` compares the whole value with the pattern, as `LIKE` of SQL does, and returns the same rows on every database :

| Pattern | Rows returned |
|---|---|
| `"Bob%"` | starting with `Bob` |
| `"%son"` | ending with `son` |
| `"%ar%"` | holding `ar` |
| `"Bob"` | which are `Bob` |
| `"B_b"`, `"B%b"` | `_` is any one character and `%` any text, wherever they stand |

- **MongoDB** has no `LIKE` : API Maker asks it with the regular expression which matches the same values, `{ first_name: /^Bob/is }` for `"Bob%"`. Every other character of the pattern is itself, so `"%1+1%"` and `"%a.b%"` need no escaping, and the case is ignored.
- **SQL instances** are asked with `LIKE` and the pattern itself. The collation of the column decides about the case, as for `=` : see [Regular expressions](https://docs.apimaker.dev/v4/docs/apis-all/query-params/find.html#regular-expressions).

## null on a SQL instance

A column of a table always exists : what it can lack is a value, and SQL calls that `NULL`.

| find | SQL |
|---|---|
| `{ "last_update": null }`, `{ "last_update": { "$eq": null } }` | `"last_update" IS NULL` |
| `{ "last_update": { "$ne": null } }` | `"last_update" IS NOT NULL` |
| `{ "last_update": { "$exists": false } }`, `{ "last_update": { "$isNull": true } }` | `"last_update" IS NULL` |
| `{ "pincode": { "$in": [ 382345, null ] } }` | `("pincode" IN (?) OR "pincode" IS NULL)` |
| `{ "pincode": { "$nin": [ 382345, null ] } }` | `"pincode" NOT IN (?)` |

> **Rows without a value and the operators which negate**
>
> For SQL a comparison with `NULL` is neither true nor false. A row whose column is `NULL` is returned by none of `$ne`, `$nin`, `$not` and `$nor` on a SQL instance, where MongoDB returns a document without the field. Ask for those rows too with `$or` : `{ "$or": [ { "pincode": { "$ne": 382345 } }, { "pincode": null } ] }`.

## Regular expressions

`$regex` takes the regular expression as a string, with the options `i` (any case), `m` (`^` and `$` at every line), `s` (`.` matches a line break) and `x` (spaces in the expression are ignored) in `$options`. A query param takes it as `/^ma/i`.

| Database | `{ "first_name": { "$regex": "^ma", "$options": "i" } }` |
|---|---|
| MongoDB | `{ first_name: /^ma/i }` |
| MySQL, MariaDB | `` `first_name` REGEXP '(?i)^ma' `` |
| PostgreSQL | `"first_name" ~ '(?i)^ma'` |
| Oracle | `REGEXP_LIKE("first_name", '^ma', 'i')` |
| SQL Server | `[first_name] LIKE 'ma%'` |

- **Case** : without `i`, MongoDB, PostgreSQL and Oracle match the case as written. MySQL, MariaDB and SQL Server follow the collation of the column, as their `=` and `LIKE` do, and most collations ignore the case.
- **SQL Server** has no regular expressions. An expression which says no more than `LIKE` can is written as `LIKE` : a text, `.`, `.*`, `[a-z]`, `\d`, with `^` and `$` at the ends. Any other expression is refused there with a message.
- **`%` at an end** (`"%son"`, `"Ma%"`, `"%ar%"`) is read as the [`$like`](https://docs.apimaker.dev/v4/docs/apis-all/query-params/find.html#of-api-maker) pattern, on every database.
- `$not` takes a regular expression too : `{ "first_name": { "$not": { "$regex": "^ma" } } }`.

## $expr on a SQL instance

`$expr` compares fields with each other. On a MongoDB instance it takes every expression of MongoDB. On a SQL instance it takes :

- the comparisons `$eq`, `$ne`, `$gt`, `$gte`, `$lt`, `$lte`, and `$and`, `$or`, `$not` around them ;
- the arithmetic `$add`, `$subtract`, `$multiply`, `$divide`, `$mod`, `$abs` ;
- a field as `"$price"`, a value as it is, and a text which starts with `$` as `{ "$literal": "$5" }`.

**Orders which cost more than their budget, tax included**

```json
{ "$expr": { "$gt": [ { "$multiply": [ "$price", 1.18 ] }, "$budget" ] } }
```

**What a MySQL instance is asked**

```sql
(`price` * ?) > `budget`
```

## Find and join : fields of related tables

A key with dots follows a column which refers to another table, as the [schema](https://docs.apimaker.dev/v4/docs/schema/schema.html) declares it, and reaches a field there. API Maker reads the related table with its own query API, keeps the ids which match, and filters the main table with them : a join across tables, databases and even instances.

> Diagram : find and join : the related table first, then the main table filtered by its ids
>
> A find on customer_id.loyalty_tier of the orders first reads the customers whose tier is GOLD, keeps their ids, then reads the orders whose customer_id is in those ids : two queries, which can be on two databases.

**Orders of gold customers : orders.customer_id refers to customers**

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

**Two levels, with $and and $or : orders of customers who live in Pune, paid or shipped**

```json
{
    "find": { "$and": [ { "customer_id.city_id.name": "Pune" }, { "$or": [ { "status": "PAID" }, { "status": "SHIPPED" } ] } ] }
}
```

- The relation comes from the schema of the table only (`table` or `collection`, `column`, `database`, `instance` on the field), as many levels as the dots : a path the schema does not describe is refused. A `deep` item of the call does not make one.
- Schema APIs only : the generated APIs (`/api/gen`) read no schema, and a dotted key goes to the database as it is (a field of an embedded document on MongoDB, an unknown column on SQL).
- The related rows are read with the permissions of the caller : a field or a table the group can not read can not be filtered on.
- A virtual field can not be used in a find and join : `Virtual field can not be used in find join feature. 'orders' is virtual field.` Filter the table which holds the reference instead : the orders of a customer, not the customers of an order.

## Types

- **Schema APIs** convert the values to the types of the schema : `{ "customer_id": "8" }` matches the number 8, a date string becomes a date, an id string an ObjectId. That holds wherever a value stands : below an operator, in the array of `$in` or `$all`, inside `$not`, `$nor` and `$elemMatch`, and for the items of an array field (`{ "scores": { "$gt": "5" } }`). `null` stays `null`.
- **Generated APIs** compare what you send : `{ "customer_id": "8" }` looks for the string.

## A column as a query param

On get all, get by id and the streams, a column name is a filter without `find` :

```text
?customer_id=1
?first_name=Bob,Alice          # one of them
?pincode>=382345               # also > < <= !=
?first_name=/^ma/i             # regular expression
?first_name=Bob&find={pincode:382345}   # both apply
```

## From code

**TypeScript**

```typescript
const rows = await g.sys.db.query({
    instance: 'mysql8', database: 'sales', collection: 'orders',
    find: { total: { $gte: 500 }, 'customer_id.loyalty_tier': 'GOLD' },
});
```

**Python**

```python
rows = await g.sys.db.query(
    'mysql8', 'sales', 'orders',
    find={'total': {'$gte': 500}, 'customer_id.loyalty_tier': 'GOLD'},
)
```

**PHP**

```php
$rows = $g->sys->db->query(
    'mysql8', 'sales', 'orders',
    find: ['total' => ['$gte' => 500], 'customer_id.loyalty_tier' => 'GOLD'],
);
```

**C#**

```csharp
var rows = await g.Sys.Db.Query(new Query("mysql8", "sales", "orders")
{
    Find = new JsonObject { ["total"] = new JsonObject { ["$gte"] = 500 }, ["customer_id.loyalty_tier"] = "GOLD" },
});
```

**Go**

```go
rows, err := g.Sys.Db.Query(am.Query{
	Instance: "mysql8", Database: "sales", Collection: "orders",
	Find: am.M{"total": am.M{"$gte": 500}, "customer_id.loyalty_tier": "GOLD"},
})
if err != nil {
	return nil, err
}
```

**Rust**

```rust
let rows = g.sys.db.query(
    Query::new("mysql8", "sales", "orders")
        .find(json!({ "total": { "$gte": 500 }, "customer_id.loyalty_tier": "GOLD" })),
)?;
```

**Java**

```java
var rows = g.sys.db.query(new Query("mysql8", "sales", "orders")
    .find(Map.of("total", Map.of("$gte", 500), "customer_id.loyalty_tier", "GOLD")));
```

**Kotlin**

```kotlin
val rows = g.sys.db.query(
    Query("mysql8", "sales", "orders")
        .find(mapOf("total" to mapOf("\$gte" to 500), "customer_id.loyalty_tier" to "GOLD")),
)
```

## Security

`find` is powerful : with it a caller reads every row a table has, unless a [pre hook](https://docs.apimaker.dev/v4/docs/apis-all/hooks/preHook-api.html) narrows it to their own rows. The [APIs Security Report](https://docs.apimaker.dev/v4/docs/apis-security/api-security-report.html) tells which tables need that hook and writes it for you. On SQL instances every value of a `find` is a parameter of the statement : a text which starts with `__:` is a value like any other, which matches the rows holding that text, never SQL.