Query and filter records

Goal

Write a HOQS query that returns exactly the records and fields you need, filtered, sorted, and paginated.

Prerequisites

The target object and its fields are applied. See From Metadata to Database.
You understand the HOQS mental model (validate-then-compile, single-hop traversal). See Querying Data with HOQS.

Steps

1. Send a query request

Every HOQS query is sent as a POST to the query endpoint, with the query string in the query field:

{
  "query": "SELECT * FROM core.Contact LIMIT 10"
}

SELECT * FROM core.Contact LIMIT 10 returns up to 10 Contact records with all fields, including system fields (id, createdAt, updatedAt), inherited fields, and user-defined fields. LIMIT is required on every top-level query.

The response includes the matching records, total count, and pagination info:

{
  "data": [
    {
      "id": "019502a4-b4e7-7f3a-8c1e-0a1b2c3d4e5f",
      "objectType": "core.Contact",
      "createdAt": "2024-03-15T10:30:00.000Z",
      "updatedAt": "2024-03-15T10:30:00.000Z",
      "firstName": "Alice",
      "lastName": "Chen",
      "email": "alice@acme.com",
      "status": "active"
    }
  ],
  "totalSize": 42,
  "hasMore": true
}

2. Filter with WHERE

Simple equality:

SELECT * FROM core.Contact WHERE status = 'active' LIMIT 50

Combine conditions with AND and OR. Without parentheses, AND binds tighter than OR:

SELECT * FROM core.Contact
WHERE (status = 'open' OR status = 'pending') AND priority = 'high'
LIMIT 50

3. Check for null with IS NULL / IS NOT NULL

SELECT * FROM core.Contact WHERE email IS NOT NULL LIMIT 50

Do not write email = NULL — HOQS’s validator rejects it and tells you to use IS NULL instead (see Querying Data with HOQS for why).

4. Match a list of values with IN / NOT IN

SELECT * FROM core.Contact
WHERE status IN ('active', 'pending', 'review')
LIMIT 100

SELECT * FROM core.Contact
WHERE status NOT IN ('closed', 'archived')
LIMIT 100

5. Match a pattern with LIKE / ILIKE

LIKE is case-sensitive, ILIKE is case-insensitive. Use % for any sequence of characters:

SELECT * FROM core.Contact WHERE lastName LIKE 'Ch%' LIMIT 50
SELECT * FROM core.Contact WHERE email ILIKE '%@acme%' LIMIT 50

6. Filter a range with BETWEEN

Inclusive on both bounds:

SELECT * FROM core.Contact WHERE age BETWEEN 25 AND 45 LIMIT 50

7. Filter by relative time with WITHIN

SELECT * FROM core.Opportunity WHERE closedDate WITHIN THIS QUARTER LIMIT 100
SELECT * FROM core.Opportunity WHERE closedDate WITHIN LAST 30 DAYS LIMIT 100
SELECT * FROM core.Contact WHERE createdAt WITHIN TODAY LIMIT 50

Available ranges: TODAY, YESTERDAY, TOMORROW, THIS/LAST/NEXT WEEK/MONTH/QUARTER/YEAR, and LAST/NEXT N DAYS/WEEKS/MONTHS/QUARTERS/YEARS.

8. Use boolean shorthand

For boolean fields, the field name alone is shorthand for = true, and NOT field for = false:

SELECT * FROM core.Contact WHERE isActive LIMIT 50
SELECT * FROM core.Contact WHERE NOT isArchived LIMIT 50

9. Select specific fields

SELECT firstName, lastName, email FROM core.Contact LIMIT 50

The response always includes the system fields (id, createdAt, updatedAt) in addition to the fields you list.

10. Sort and paginate

SELECT * FROM core.Contact ORDER BY lastName ASC LIMIT 50
SELECT * FROM core.Opportunity ORDER BY createdAt DESC LIMIT 25

Default sort direction is ASC. Use OFFSET for pagination:

SELECT * FROM core.Contact ORDER BY lastName ASC LIMIT 50 OFFSET 100

11. Traverse a lookup with dot notation

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

The first segment (account) must be a lookup field on the object being queried. The second segment (name, industry) is a field on the lookup’s target. Only single-hop traversal is supported — account.name works, account.owner.name does not.

Verification

Run your query and check totalSize and hasMore in the response against what you expect. If a field name is misspelled, the error response includes the position and a suggestion, e.g. Field 'statux' does not exist on object 'core.Contact'. (at 1:42) Did you mean 'status'?.