Thunderjet - RMB indices Cheat-sheet

Avoid using ginIndex if possible. It is slower and takes more space than index or fullTextIndex.

Use-cases:

Exact search for integer, boolean, date, enum
CQL: field==value
for numbers: field =/number value

  index:
      "fieldName": "field",
      "caseSensitive": true,
      "removeAccents": false,
      "sqlExpression": "(jsonb->>'field')",
      "sqlExpressionQuery": "$"

Note that, when using "field==value" (without wildcards), RMB will generate a LIKE query, and complain a LIKE index is missing if there is no "index" or "ginIndex". This does not mean a ginIndex is needed. The b-tree index is used even when the SQL query is using "LIKE". A ginIndex is only needed when using wildcards.
There is an RMB issue when using <= or >=: it ignores sqlExpression when creating the SQL from CQL - so in this case sqlExpression and sqlExpressionQuery should not be used.

Left or right LIKE
(this can be useful for instance to look for a year in a date or a date in a date-time, and end users could add wildcards too)
CQL: field==value* or field==*value

  ginIndex:
      "fieldName": "field",
      "caseSensitive": true unless false is needed,
      "removeAccents": false unless true is needed

use sqlExpression and sqlExpressionQuery if the field is always shorter than 600 characters
Also consider adding an index type of index to use when users can add wildcards but usually don't.

To look for a value in an array of 1-word strings (for instance an array of ids)
CQL: field=value

  fullTextIndex:
      "fieldName": "field",
      "caseSensitive": false,
      "removeAccents": false unless true is needed

To find a substring in a string or a value in an array using ginIndex/LIKE (avoid doing that if possible)
searching for *range* also finds stranger
CQL: field==*value*

  ginIndex:
      "fieldName": "field",
      "caseSensitive": true unless false is needed,
      "removeAccents": only if needed (do not enable for ids)

To find a word in a string or a value in an array using fullTextIndex/tsvector, supports right truncation
(this performs better than ginIndex/LIKE but does not support caseSensitive:true and punctuation is ignored)
CQL: field=value
CQL: field=value*

  fullTextIndex:
      "caseSensitive": false (case sensitive is not possible for fullTextIndex)
      "removeAccents": only if needed (do not enable for ids)

To find a property value in an array of objects:
CQL: "field =/@property2 value" or for the default property(in arraySubfield): "field = value"

  fullTextIndex: (ginIndex is possible too but fullTextIndex is better to look for ids)
      "fieldName": "field",
      "caseSensitive": false,
      "removeAccents": false,
      "arraySubfield": "property1",
      "arrayModifiers": ["property1", "property2"]

(arraySubfield and arrayModifiers can also be used for more complex cases)
RMB documentation about /@ array searches

To sort by a given field
CQL: sortby metadata.updatedDate/sort.descending
Create a custom index. Use lower(f_unaccent()), RMB will ignore caseSensitive/removeAccents when generating the SQL query.

CREATE INDEX IF NOT EXISTS purchase_order_updated_date_sort ON ${myuniversity}_${mymodule}.purchase_order
  (left(lower(f_unaccent(jsonb->'metadata'->>'updatedDate')), 600),
    lower(f_unaccent(jsonb->'metadata'->>'updatedDate')));

To look for something different from a given value
CQL: field <> value
In general, avoid using an index altogether. RMB will complain about the lack of an index, but it expects a b-tree index which would be useless.
Create a custom index with a more complex expression if necessary, for instance:

CREATE INDEX IF NOT EXISTS purchase_order_no_acq_unit ON ${myuniversity}_${mymodule}.purchase_order
  ((lower(f_unaccent(jsonb->>'acqUnitIds')) NOT LIKE lower(f_unaccent('[]'))));

Note the use of "NOT LIKE" used by RMB when generating the SQL; this will behave like SQL "<>" when the value has no wildcard.

To let users do a fast exact search by default, but allow wildcards (for instance for poNumber)
If it's a simple property, create both an index and ginIndex and use CQL: field == value
If it's an array or json, use fullTextIndex, only allow a wildcard at the end, and use CQL: field = value