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.
conditionA comparison such as
Required: At least onestatus = "paid"ortotal >= 100, a groupAND(…),OR(…)orNOT(…), or anEXPR("…")predicatefieldThe field compared, named as the answer names it (
Required: Yes, in a comparisoncontentType,financialStatus); case does not mattervalueA string, a number,
Required: Yes, in a comparisontrue,false,null, or a list["a", "b"]
Supported operators
A condition can use any of these operators. Each row shows a condition and what it keeps.
| Operator | Example | Meaning |
|---|---|---|
= | financialStatus = "paid" | Keeps only the orders that have been paid; refunded and unpaid orders are left out |
IS | financialStatus 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 NOT | country IS NOT "AU" | Keeps the orders shipped outside Australia |
> | total > 500 | Keeps only the large orders, worth more than 500; an order worth exactly 500 is left out |
IS GREATER THAN | total IS GREATER THAN 500 | The same test as >: keeps the orders worth more than 500 |
>= | total >= 100 | Keeps orders worth 100 or more, the ones a free shipping rule would apply to; smaller orders are left out |
IS GREATER THAN OR EQUAL | total IS GREATER THAN OR EQUAL 100 | The same test as >=: keeps the orders worth 100 or more |
< | shipping < 15 | Keeps the orders whose shipping charge was under 15 |
IS LESS THAN | shipping IS LESS THAN 15 | The same test as <: keeps the orders with a shipping charge under 15 |
<= | discount <= 0 | Keeps the orders that carried no discount |
IS LESS THAN OR EQUAL | discount IS LESS THAN OR EQUAL 0 | The same test as <=: keeps the orders that carried no discount |
LIKE | slug 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 LIKE | slug NOT LIKE "%draft%" | Keeps entries whose slug never mentions draft |
CONTAINS | slug CONTAINS "gift" | Keeps entries whose slug mentions gift, in any letter case |
DOES NOT CONTAIN | slug DOES NOT CONTAIN "test" | Keeps entries whose slug never mentions test, so test pages stay out |
STARTS WITH | id STARTS WITH "cal" | Keeps the entries whose id begins with cal, the calendar links |
ENDS WITH | id 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 500 | Keeps orders worth from 50 up to 500, both ends included |
NOT BETWEEN … AND … | shipping NOT BETWEEN 0 AND 5 | Keeps 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 NULL | publishedAt IS NULL | Keeps entries that have never been published |
IS NOT NULL | publishedAt IS NOT NULL | Keeps entries that have been published at some time |
IS BLANK | slug IS BLANK | Keeps entries that have no slug, none at all or an empty one. The same test as IS NULL |
IS NOT BLANK | slug IS NOT BLANK | Keeps entries whose slug has text in it. The same test as IS NOT NULL |
MATCHES | title MATCHES "family" | Keeps the assets whose title is about family, found by text search and scored so the closest title ranks first |
MATCHES ANY | title MATCHES ANY ["family", "sunset"] | Keeps the assets about family, about sunsets, or about both |
MATCHES ALL | title MATCHES ALL ["family", "sunset"] | Keeps only the assets about family and sunsets together |
SIMILARITY | title SIMILARITY "family at sunset" | Keeps the assets whose whole title reads much like the phrase |
FUZZY | title 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.
| Error | What happened | How to recover |
|---|---|---|
HTQL-1003 Unsupported syntax | The 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 expression | A bare string as the condition, such as WHERE("status = 'paid'") | Write WHERE(status = "paid") |
HTQL-2001 | A condition the engine cannot evaluate on this table: the statement is refused, never run without it | Check 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
HAVINGto filter after aggregation inLEARN. - A second
FILTERin the sameLOOKUPreplaces the first: combine conditions in oneFILTER. WHERE()is a synonym forFILTER().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 ofORcomparisons. - Filter in the statement that reads the data, so the database narrows the rows before they leave it.