HOQS Query Language Reference

HOQS (Henceforth Object Query Syntax) is a metadata-aware query language for filtering, sorting, aggregating, and traversing records. Queries are written as strings, validated against compiled metadata, and compiled to parameterized PostgreSQL SQL against the generated views.

Grammar Overview

A HOQS query has the following structure:

SELECT { * | field [, field | aggregate | timeFunction]... }
FROM module.Object
[WHERE predicate]
[GROUP BY groupItem [, groupItem]...]
[HAVING aggregatePredicate]
[INCLUDE relationship [(selectClause)] [WHERE predicate] [ORDER BY ...] [LIMIT n]
  [INCLUDE ...]]
[ORDER BY orderItem [, orderItem]...]
LIMIT n
[OFFSET n]

LIMIT is required on every top-level query.

Clauses

SELECT

Selects all fields or a list of specific fields, aggregates, and time functions.

SELECT *
SELECT firstName, lastName, email
SELECT COUNT(*) AS total, status
SELECT YEAR(closedDate) AS yr, SUM(amount) AS revenue

SELECT * returns all fields from the object’s view, including system fields (id, createdAt, updatedAt) and inherited fields.

Named fields return the system fields plus only the listed fields.

FROM

Specifies the target object using its module ID and object key.

FROM core.Account
FROM core.Contact

WHERE

Filters records using comparison operators, logical operators, and special predicates.

WHERE status = 'active'
WHERE age >= 18 AND age <= 65
WHERE (status = 'open' OR status = 'pending') AND priority = 'high'
WHERE NOT isArchived

See Operators and Predicates for the full set.

GROUP BY

Groups results for aggregate queries. Accepts field references and time functions.

GROUP BY status
GROUP BY YEAR(closedDate), QUARTER(closedDate)
GROUP BY status, MONTH(closedDate) AS m

Fields in SELECT that are not aggregates must appear in GROUP BY.

HAVING

Filters groups by aggregate conditions. Only valid with GROUP BY or aggregate SELECT.

HAVING COUNT(*) > 5
HAVING SUM(amount) >= 1000 AND AVG(amount) > 100

INCLUDE

Fetches child records for a relationship defined by a lookup’s toInverseKey. Supports nested INCLUDEs for multi-level child traversal.

INCLUDE contacts
INCLUDE contacts (firstName, email) WHERE status = 'active' ORDER BY lastName ASC LIMIT 5
INCLUDE contacts
  INCLUDE opportunities WHERE stage = 'Closed Won' LIMIT 10

Each INCLUDE generates a separate SQL query per parent row, stitched into the response as a nested array.

ORDER BY

Sorts results by fields, aggregates, or time functions. Default direction is ASC.

ORDER BY name ASC
ORDER BY createdAt DESC
ORDER BY COUNT(*) DESC
ORDER BY YEAR(closedDate) ASC

LIMIT / OFFSET

Controls result pagination. LIMIT is required. OFFSET is optional.

LIMIT 50
LIMIT 50 OFFSET 100

Operators

Comparison Operators

Operator	Meaning
`=`	Equal
`!=`	Not equal
`<`	Less than
`<=`	Less than or equal
`>`	Greater than
`>=`	Greater than or equal

Ordering operators (<, <=, >, >=) are valid on orderable types: Int, Decimal, Text, Timestamp, Date.

Logical Operators

Operator	Meaning
`AND`	Both conditions must be true
`OR`	Either condition must be true
`NOT`	Negates the following predicate

Precedence (highest to lowest): NOT, AND, OR. Use parentheses to override.

WHERE (status = 'open' OR status = 'pending') AND NOT isArchived

NOT can precede any predicate without parentheses:

WHERE NOT status = 'closed'
WHERE NOT name IN ('test', 'demo')

Predicates

Comparison

field = value
field != value
field > value

Boolean Shorthand

A bare field name is treated as field = true:

WHERE isActive           -- equivalent to: WHERE isActive = true
WHERE NOT isActive       -- equivalent to: WHERE isActive = false

IN List

field IN ('value1', 'value2', 'value3')
field NOT IN ('a', 'b')

Maximum list size: 500 values.

IS NULL / IS NOT NULL

field IS NULL
field IS NOT NULL

Using field = NULL or field != NULL is a validation error. The validator suggests using IS NULL / IS NOT NULL instead, because = NULL in SQL evaluates to NULL (not true or false), silently returning zero rows.

LIKE / ILIKE

Pattern matching on text fields. LIKE is case-sensitive, ILIKE is case-insensitive.

field LIKE 'Acme%'
field ILIKE '%search%'

BETWEEN

Inclusive range check on orderable fields.

field BETWEEN low AND high

Compiles to field >= low AND field <= high. Both bounds are inclusive. Valid on Int, Decimal, Text, Timestamp, Date fields.

WHERE age BETWEEN 18 AND 65
WHERE createdAt BETWEEN '2024-01-01' AND '2024-12-31'

WITHIN (Relative Time)

Filters timestamp or date fields by relative time ranges. No parameter binding — compiles to NOW()-based SQL expressions.

field WITHIN TODAY
field WITHIN YESTERDAY
field WITHIN TOMORROW
field WITHIN THIS WEEK
field WITHIN THIS MONTH
field WITHIN THIS QUARTER
field WITHIN THIS YEAR
field WITHIN LAST WEEK
field WITHIN LAST MONTH
field WITHIN NEXT QUARTER
field WITHIN LAST 30 DAYS
field WITHIN NEXT 6 MONTHS

Supported time units: DAY(S), WEEK(S), MONTH(S), QUARTER(S), YEAR(S).

Aggregates

Function	Description
`COUNT(*)`	Count all rows
`COUNT(field)`	Count non-null values
`COUNT(DISTINCT field)`	Count unique non-null values
`SUM(field)`	Sum (numeric fields only)
`AVG(field)`	Average (numeric fields only)
`MIN(field)`	Minimum (orderable fields only)
`MAX(field)`	Maximum (orderable fields only)

Aggregates require an alias in SELECT:

SELECT COUNT(*) AS total, SUM(amount) AS revenue FROM core.Opportunity LIMIT 1

DISTINCT is only valid inside COUNT.

Time Functions

Extract a date/time component from a Timestamp or Date field. Used in SELECT and GROUP BY.

Function	SQL Output
`YEAR(field)`	`DATE_TRUNC('year', field)`
`QUARTER(field)`	`DATE_TRUNC('quarter', field)`
`MONTH(field)`	`DATE_TRUNC('month', field)`
`WEEK(field)`	`DATE_TRUNC('week', field)`
`DAY(field)`	`DATE_TRUNC('day', field)`
`HOUR(field)`	`DATE_TRUNC('hour', field)`
`MINUTE(field)`	`DATE_TRUNC('minute', field)`

SELECT YEAR(closedDate) AS yr, COUNT(*) AS deals
FROM core.Opportunity
GROUP BY YEAR(closedDate)
ORDER BY yr ASC
LIMIT 100

Dot-Notation (Parent Traversal)

Access fields on a parent object through a lookup using dot notation:

WHERE account.industry = 'Technology'
SELECT account.name, firstName FROM core.Contact LIMIT 10
ORDER BY account.name ASC

The first segment must be a lookup field. The second segment is a field on the lookup’s target object. Only single-hop traversal is supported (e.g., account.name but not account.owner.name).

Dot-notation compiles to a LEFT JOIN against the parent object’s view.

Value Types

Type	Syntax	Examples
String	Single quotes	`'hello'`, `'2024-01-01'`
Integer	Bare digits	`42`, `0`, `-1`
Decimal	Digits with dot	`3.14`, `100.0`
Boolean	`true` / `false`	`true`, `false`
Null	`NULL`	`NULL`

Limits and Constraints

Limit	Value	Rationale
IN list maximum size	500	Prevents SQL parameter bloat
Expression nesting depth	20	Limits query plan complexity
INCLUDE clauses per level	10	Controls N+1 query fan-out
INCLUDE nesting depth	3	Bounds recursive child queries

Validation Errors

The validator checks every query against the compiled metadata before compilation. Errors include source position (line and column) when available.

Error Code	Description
`UNKNOWN_FIELD`	Field does not exist on the target object. Includes “Did you mean?” suggestions for close matches.
`TYPE_MISMATCH`	Operator or function is not valid for the field’s type (e.g., LIKE on an Int field).
`NULL_COMPARISON`	Using `= NULL` or `!= NULL` instead of `IS [NOT] NULL`.
`LIMIT_EXCEEDED`	A safety limit was exceeded (IN list size, expression depth, INCLUDE count or depth).
`NOT_A_LOOKUP`	Dot notation used on a field that is not a lookup.
`INVALID_TRAVERSAL`	Multi-hop dot notation attempted (not yet supported).
`INVALID_RELATIONSHIP`	INCLUDE references a relationship that does not exist.
`INVALID_AGGREGATE`	Aggregate used in wrong context (e.g., in WHERE instead of HAVING, or with GROUP BY + SELECT *).
`FIELD_NOT_IN_GROUP_BY`	Non-aggregate field in SELECT not listed in GROUP BY.
`AMBIGUOUS_REFERENCE`	Name matches both a field and a SELECT alias.
`INVALID_HAVING`	Non-aggregate predicate in HAVING clause.

Compilation

HOQS compiles to parameterized PostgreSQL SQL against the object’s generated view. All user-provided values become bind parameters ($1, $2, …) — no string interpolation.

Source query:

SELECT firstName, email
FROM core.Contact
WHERE status = 'active' AND account.industry = 'Technology'
ORDER BY lastName ASC
LIMIT 25 OFFSET 50

Compiled SQL:

SELECT v."id", v."created_at", v."updated_at", v."first_name", v."email"
FROM "hf_core__contact__view" v
LEFT JOIN "hf_core__account__view" "j_account" ON v."account_id" = "j_account"."id"
WHERE v."status" = $1 AND "j_account"."industry" = $2
ORDER BY v."last_name" ASC
LIMIT 25 OFFSET 50

A separate count query is generated for pagination:

SELECT COUNT(*)
FROM "hf_core__contact__view" v
LEFT JOIN "hf_core__account__view" "j_account" ON v."account_id" = "j_account"."id"
WHERE v."status" = $1 AND "j_account"."industry" = $2