Deep Query

Deep Query is the object-model query language mode — it speaks the same Hibernate Query Language (HQL) the platform uses internally. The editor is Monaco-based with autocomplete fed by the schema endpoint, so you get entity / field name suggestions without leaving the page.

Reference: HQL is essentially "SQL over the JPA object graph". If you've used JPQL or HQL before, this is the same. If you haven't, Hibernate's HQL guide is the canonical reference.

When to use Deep

• Multi-entity joins beyond what the Quick mode dot-notation can express.
• Polymorphic queries against subclass hierarchies.
• Aggregations + sub-selects.
• Saving an executable snippet for the team.

For everyday slices, Quick Query remains the path of least resistance.

Editor essentials

The Deep editor uses Monaco's built-in sql language for syntax highlighting (HQL is close enough that the SQL grammar reads cleanly) and a custom completion provider:

Type	Trigger	Source
Keywords	always	SELECT, FROM, WHERE, GROUP BY, JOIN, LIMIT, …
Entity names	always	GET /api/v1/workbench/schema/entities (Java simple names: BillingAccount, Subscription, Invoice)
Field names	after <alias>.	GET /api/v1/workbench/schema/entities/{entity}

Type from B and the autocomplete proposes BillingAccount. Bind an alias from BillingAccount ba and type ba. — the field list appears.

A first query

from BillingAccount ba
where ba.code like 'PX_%'
order by ba.auditable.created desc

Click Run sync to fire GET /api/rest/query?query=…. Up to 1 000 rows return as JSON. Each row is the full BillingAccount entity (with FKs lazily loaded if you read them).

Projections

To return a flat row of specific fields, list them after select:

select ba.code, ba.status, ba.customerAccount.code
from BillingAccount ba
where ba.code like 'PX_%'

The result table on the right uses the keys of the first row to derive columns (here: code, status, customerAccount.code).

Joins

HQL joins via dot-navigation — there's no explicit JOIN ... ON for related entities (Hibernate writes the SQL for you):

from Invoice i
where i.billingAccount.customerAccount.customer.code = 'CUSTOMER_ABC'
  and i.status = 'VALIDATED'

For unrelated entities (rare in Billerang's model), explicit joins on a key column work the same as JPQL.

Aggregations

select i.status, count(i.id), sum(i.amountWithTax)
from Invoice i
where i.auditable.created > :since
group by i.status
order by 2 desc

:since is a named parameter — pass it on the URL:

GET /api/rest/query?query=...&since=2025-01-01T00:00:00

Group-by and sum / count / avg / min / max all work.

Polymorphic queries

If your query target has subclasses (e.g. OneShotChargeTemplate, RecurringChargeTemplate, UsageChargeTemplate all extend ChargeTemplate), HQL on the parent walks all subclasses by default:

from ChargeTemplate ct
where ct.code like 'BLR_%'

To restrict to one subclass, name it directly (from RecurringChargeTemplate ...).

What's blocked

The /api/rest/query endpoint is hardened: HQL must start with from, and the validator rejects DDL / DML keywords (update, delete, insert, drop, alter, into, exec, …). Read-only by contract.

Limits

• 1 000 rows hard cap (governor knob workbench.governor.maxRowsSync).
• 30 s sync timeout (workbench.governor.maxQueryDurationSyncSec).
• Read-only — DDL / DML are rejected with a 400.

Async support for Deep follows the same pattern as Quick — save the query, run async, poll the resulting QueryExecutionResult.

Saving

Saving a Deep query persists queryType=HQL and the editor body in generated_query on the report_query row. Reopening the saved query repopulates the editor in Deep mode.

When to step up to Expert

Switch to Expert Query only when:

• You need a column the JPA mapping doesn't expose (e.g. a cf_values jsonb column directly).
• You're investigating an issue that requires looking at the physical schema.
• You're sure the SQL you're about to write doesn't need to be reusable.

For everything else, Deep is the right power level — and unlike Expert, it doesn't bypass any security layer Opencell provides.