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:

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

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

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