Criteria Objects
Many Massive functions use criteria objects to build WHERE
clauses. Although they are principally used in query functions, there are other uses for them. In particular, bulk updates use criteria objects to filter the records being modified.
A criteria object is a "plain old JavaScript object" where keys represent the fields to search and values are prepared statement parameters. The keys or
and and
are special and take an array of nested criteria objects. At least one of the nested criteria must be fully matched for a record to be included in the resultset with the former, and all for the latter. or
and and
may be nested recursively at any depth.
// this will search for all active records where the
// name contains 'homepage' or the JSON 'stats' field
// shows more than 5 runs
const criteria = {
is_active: true,
or: [{
'name like': '%homepage%'
}, {
'stats.runs >': 5
}]
};
Operations
Keys in a criteria object may contain an operator which is converted to a SQL operator in the WHERE
clause. If no operator is provided, the predicate will test for equality.
Text operator keys are case-insensitive.
Scalar Comparison
Format | SQL operator | Description |
---|---|---|
nothing or = |
= |
Equality |
! , != , <> |
<> |
Inequality |
< |
< |
Less than |
<= |
<= |
Less than or equal |
> |
> |
Greater than |
>= |
>= |
Greater than or equal |
nothing or = |
IN |
Test whether value is in an array of scalar expressions |
BETWEEN |
BETWEEN |
Test whether value is between the [lower, upper] bounds of a 2-element array |
IS |
IS |
Explicit equality test for NULL and boolean values |
IS NOT |
IS NOT |
Explicit inequality test for NULL and boolean values |
IS DISTINCT FROM |
IS DISTINCT FROM |
Difference test with NULL considered a fixed value |
IS NOT DISTINCT FROM |
IS NOT DISTINCT FROM |
Equality test with NULL considered a fixed value |
Arrays
Format | SQL operator | Description |
---|---|---|
@> |
@> |
Array contains |
<@ |
<@ |
Array contained in |
&& |
&& |
Array overlaps |
Pattern Matching
Format | SQL operator | Description |
---|---|---|
~~ , LIKE |
LIKE |
Case-sensitive string equality with % and _ wildcards |
!~~ , NOT LIKE |
NOT LIKE |
Case-sensitive string difference with % and _ wildcards |
~~* , ILIKE |
ILIKE |
Case-insensitive string equality with % and _ wildcards |
!~~* , NOT ILIKE |
NOT ILIKE |
Case-insensitive string difference with % and _ wildcards |
Regular Expressions
Format | SQL operator | Description |
---|---|---|
SIMILAR TO |
SIMILAR TO |
SQL regular expression match |
NOT SIMILAR TO |
NOT SIMILAR TO |
SQL regular expression mismatch |
~ |
~ |
Case-sensitive POSIX regular expression match |
!~ |
!~ |
Case-sensitive POSIX regular expression mismatch |
~* |
~* |
Case-insensitive POSIX regular expression match |
!~* |
!~* |
Case-insensitive POSIX regular expression mismatch |
Casting
PostgreSQL can cast values with the ::
operator. Massive's criteria object supports this exactly as in SQL. For example, to convert a UUID field to TEXT for pattern matching, you could create a criteria object as follows:
const criteria = {
'my_uuid::text LIKE': '12345%'
};
JSON Traversal
Massive supports searching in JSON and JSONB fields using idiomatic JavaScript paths. Use dots to traverse fields, and [] brackets to denote array indices. JSON traversal may be combined with SQL operations and casts (the cast applies to the value in the JSON field at the specified path, not to the JSON field itself).