Criteria Objects

Many Massive functions, from find and count to update and destroy, use criteria objects to build query WHERE clauses and filter the rows retrieved or affected. A criteria object is a "plain old JavaScript object" mapping each field name to its expected value which will qualify a record for inclusion in the result set. The field key may also specify JSON traversal, casts, and operations other than equality, in order:

Field name; with join queries, the originating table may need to be specified (e.g. mytable.myfield or even myschema.mytable.myfield)
(Optional) JSON traversal, in .property[array-index] notation
(Optional) cast, as ::destination-type
(Optional) operator, if testing something other than equality or membership IN an array

An empty criteria object ({}) yields an always-true predicate which allows retrieving, updating, or deleting every record in the specified table or view. Criteria objects may not be null or undefined.

The keys $or and $and are reserved for arrays 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 to any depth.

// this will search for all active records where the
// name contains 'homepage' or the 'runs' property of
// the JSON 'stats' field is greater than 5, after
// being cast to INT

const criteria = {
  is_active: true,
  or: [{
    'name like': '%homepage%'
  }, {
    'stats.runs::INT >': 5
  }]
};

Operations
Casting
JSON Traversal
Hand-Written SQL

Operations

The default behavior for an individual criterion (i.e. a single key-value pair in the criteria object) is to test for equality: record values must match the criterion's right-hand side value.

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`	Presence in value array
`!`, `!=`, `<>`	`NOT IN`	Absence from value array
`BETWEEN`	`BETWEEN`	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

JSON and JSONB

Consult the PostgreSQL docs for more information and examples. @> and <@ are not currently supported for JSON/JSONB.

Format	SQL operator	Description
`?`	`?`	Is the value string a top-level key?
`?\|`	`?\|`	Is any string in the value array a top-level key?
`?&`	`?&`	Are all strings in the value array top-level keys?
`@?`	`@?`	JSONPath is defined
`@@`	`@@`	JSONPath matches predicate

Arrays

More information and examples in the PostgreSQL docs.

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

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), which should be applied after.

const criteria = {
  'jsonfield.arr[0].element::INT <': 5
};

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%'
};

Hand-Written SQL

In some cases, such as certain range or geographic (with PostGIS) operations, standard criteria object processing may not be quite up to the task. Readable.where() should be preferred in these situations. However, it is also possible to use find() and other criteria-using functions with a special criteria object format which provides pre-written conditions and a parameter list, bypassing ordinary criteria object processing.

The same cautions about interpolating user input into raw SQL as with where() or options.exprs apply here.

{
  conditions: 'x = $1 and y = $2',
  params: [3, 4],
  where: { an optional nested criteria object }
}