FILTER

Say which records you mean, and HTQLDB reads or changes only those.

Syntax

Write the conditions once, inside one FILTER.

FILTER(condition, condition, …)
WHERE(condition, condition, …)

Every condition listed must hold: conditions separated by commas are combined with AND.

Parameters and arguments

Each condition is a comparison, a group of conditions or an expression.

  • condition

    A comparison such as status = "paid" or total >= 100, a group AND(…), OR(…) or NOT(…), or an EXPR("…") predicate

    Required: At least one
  • field

    The field compared, named as the answer names it (contentType, financialStatus); case does not matter

    Required: Yes, in a comparison
  • value

    A string, a number, true, false, null, or a list ["a", "b"]

    Required: Yes, in a comparison

Supported operators

A condition can use any of these operators. Each row shows a condition and what it keeps.

OperatorExampleMeaning
=financialStatus = "paid"Keeps only the orders that have been paid; refunded and unpaid orders are left out
ISfinancialStatus IS "paid"The same test as =, read as a sentence: keeps the paid orders
!=channel != "pos"Keeps the orders that did not come through the point of sale
<>state <> "NSW"Keeps the orders shipped anywhere except New South Wales
IS NOTcountry IS NOT "AU"Keeps the orders shipped outside Australia
>total > 500Keeps only the large orders, worth more than 500; an order worth exactly 500 is left out
IS GREATER THANtotal IS GREATER THAN 500The same test as >: keeps the orders worth more than 500
>=total >= 100Keeps orders worth 100 or more, the ones a free shipping rule would apply to; smaller orders are left out
IS GREATER THAN OR EQUALtotal IS GREATER THAN OR EQUAL 100The same test as >=: keeps the orders worth 100 or more
<shipping < 15Keeps the orders whose shipping charge was under 15
IS LESS THANshipping IS LESS THAN 15The same test as <: keeps the orders with a shipping charge under 15
<=discount <= 0Keeps the orders that carried no discount
IS LESS THAN OR EQUALdiscount IS LESS THAN OR EQUAL 0The same test as <=: keeps the orders that carried no discount
LIKEslug LIKE "%pricing%"Keeps entries whose slug has pricing in it. The pattern is taken as written: % stands for any run of characters, _ for one, and letter case counts
NOT LIKEslug NOT LIKE "%draft%"Keeps entries whose slug never mentions draft
CONTAINSslug CONTAINS "gift"Keeps entries whose slug mentions gift, in any letter case
DOES NOT CONTAINslug DOES NOT CONTAIN "test"Keeps entries whose slug never mentions test, so test pages stay out
STARTS WITHid STARTS WITH "cal"Keeps the entries whose id begins with cal, the calendar links
ENDS WITHid ENDS WITH "Google"Keeps the entries whose id ends with Google
IN [ … ]contentType IN ["navLink", "page"]Keeps entries that are navigation links or pages
NOT IN [ … ]contentType NOT IN ["navLink", "page"]Keeps every kind of entry except navigation links and pages
BETWEEN … AND …total BETWEEN 50 AND 500Keeps orders worth from 50 up to 500, both ends included
NOT BETWEEN … AND …shipping NOT BETWEEN 0 AND 5Keeps orders whose shipping charge fell outside 0 to 5, which leaves out free and nearly free shipping
RANGE(a-b, c-d)discount RANGE(1-10, 25-50)Keeps orders with a small discount, 1 to 10, or a deep one, 25 to 50, and leaves out those in between
NOT IN RANGE(a-b)tax NOT IN RANGE(0-1)Keeps orders that paid more than a token amount of tax, leaving out those with none or almost none
IS NULLpublishedAt IS NULLKeeps entries that have never been published
IS NOT NULLpublishedAt IS NOT NULLKeeps entries that have been published at some time
IS BLANKslug IS BLANKKeeps entries that have no slug, none at all or an empty one. The same test as IS NULL
IS NOT BLANKslug IS NOT BLANKKeeps entries whose slug has text in it. The same test as IS NOT NULL
MATCHEStitle MATCHES "family"Keeps the assets whose title is about family, found by text search and scored so the closest title ranks first
MATCHES ANYtitle MATCHES ANY ["family", "sunset"]Keeps the assets about family, about sunsets, or about both
MATCHES ALLtitle MATCHES ALL ["family", "sunset"]Keeps only the assets about family and sunsets together
SIMILARITYtitle SIMILARITY "family at sunset"Keeps the assets whose whole title reads much like the phrase
FUZZYtitle FUZZY "sunst"Keeps the assets whose title holds a word close to sunst, so a misspelling still finds them
AND(…)AND(published = true, archived = false)Keeps the live entries: published and not archived
OR(…)OR(contentType = "navLink", contentType = "storeLink")Keeps entries that are either kind of link
NOT(a)NOT(archived = true)Keeps every entry that has not been archived
NOT(a, b)NOT(published = false, archived = true)Leaves out only the entries that are both unpublished and archived; an entry with just one of the two stays
EXPR("…")EXPR("$total > $subtotal")A condition written as SQL over the table's own columns, each written $column, such as orders where the total is above the subtotal. It is reserved for the platform's own statements: a statement run as your account is refused

IS GREATER THAN EQUAL and IS LESS THAN EQUAL are accepted as well. LIKE and NOT LIKE take their pattern as written and letter case counts; CONTAINS, DOES NOT CONTAIN, STARTS WITH and ENDS WITH find their text as itself and ignore letter case. A missing value is null or empty text, so IS NULL and IS BLANK are one test.

Examples

Two conditions in one FILTER: both must hold.

Return type and behaviour

FILTER narrows the records of the statement it belongs to, and that statement returns its own result. A condition the engine cannot evaluate refuses the whole statement rather than running it without that condition, so a record you meant to leave out is never included by mistake.

Errors and exceptions

Each error says what happened and how to recover.

ErrorWhat happenedHow to recover
HTQL-1003 Unsupported syntaxThe condition is not written in HTQL's grammar, for example an unknown operator such as ~Use an operator from the list above
HTQL-1009 Invalid expressionA bare string as the condition, such as WHERE("status = 'paid'")Write WHERE(status = "paid")
HTQL-2001A condition the engine cannot evaluate on this table: the statement is refused, never run without itCheck the column's name and type
Placement and context

FILTER sits where you name the records. Inside LOOKUP, it goes after the table name, among its other clauses. In UPDATE and REMOVE, after the target table:

UPDATE {"status": "archived"} IN orders FILTER(status = "cancelled")
REMOVE drafts FILTER(created_at < "2026-01-01")

In LEARN, FILTER limits the rows before they are grouped; HAVING limits the groups after.

Limitations

Three things to know when you write a FILTER.

  • Use HAVING to filter after aggregation in LEARN.
  • A second FILTER in the same LOOKUP replaces the first: combine conditions in one FILTER.
  • WHERE() is a synonym for FILTER(). IF() is a formula, not a filter.
Permissions and security

FILTER grants no access of its own: the statement it belongs to needs read access (LOOKUP) or write access (UPDATE, REMOVE) to the table. Row-level security confines every statement to the caller's own tenant or member, so a FILTER never reaches another tenant's records, whatever it names.

Best practices
  • Put every condition in one FILTER.
  • Use IN [ … ] for a set of values rather than a chain of OR comparisons.
  • Filter in the statement that reads the data, so the database narrows the rows before they leave it.