This page View Markdown Open in ChatGPT Open in Claude

find¶

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)

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

In the body of the query APIs

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

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

"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
`$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
`$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.

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

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

What a MySQL instance is asked

(`price` * ?) > `budget`

A key with dots follows a column which refers to another table, as the schema 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.

Orders of gold customers : orders.customer_id refers to customers

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

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

{
    "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 :

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

TypeScriptPythonPHPC#GoRustJavaKotlin

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

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

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

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" },
});

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
}

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

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

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 narrows it to their own rows. The APIs Security Report 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.