Criteria Objects

Many Massive functions use criteria objects to build WHERE clauses. Although these are principally used in query functions, they have other uses: in particular, bulk updates use criteria objects to filter the records being modified.

A criteria object is a "plain old JavaScript object" mapping table or view fields (plus optional JSON traversal, operators, and casts) to the values being matched. 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 to 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

The default behavior for an individual criterion (i.e. a single key-value pair in the criteria object) is to test for equality: a record's value for the field specified by the criterion's key must equal the criterion's value. However, many other operators can be used by appending them to the criterion key.

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

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

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