jq!{} is a macro that turns a small subset of jq syntax into JSON_TABLE-backed SQL. Use it when you want to iterate, filter, and project from a JSON document into a flat list.

Why a separate macro?

json::get_text(j, path) extracts one field. json::get_json(j, path) returns a nested document. What you often want is “iterate this JSON array, filter to records matching X, and project field Y.” That’s a relational operation — and Oracle’s JSON_TABLE is exactly the right primitive. jq!{} is a convenient way to write it.

The basic shape

let names: list<text> = jq!{ j | .users[] | .name }.collect();

• j is the source JSON variable.
• | .users[] | iterates the array at $.users.
• | .name projects each element’s name field.

The lowering:

SELECT jt.v
  BULK COLLECT INTO l_names
  FROM JSON_TABLE(p_j, '$.users[*]'
    COLUMNS (v VARCHAR2(4000) PATH '$.name')) jt;

With a filter

let adults: list<text> = jq!{
    j | .users[] | select(.age >= 18) | .name
}.collect();

lowers to:

SELECT jt.v
  BULK COLLECT INTO l_adults
  FROM JSON_TABLE(p_j, '$.users[*]'
    COLUMNS (v  VARCHAR2(4000) PATH '$.name',
             f0 NUMBER         PATH '$.age')) jt
  WHERE jt.f0 >= 18;

Filter fields that aren’t in the projection get auto-added to the COLUMNS clause as synthetic f0, f1, … columns. The WHERE clause references the alias, not nested JSON_VALUE calls.

With multiple filters

and and or combine select(...) predicates:

let working_age: list<text> = jq!{
    j | .users[] | select(.age >= 18 and .age <= 65) | .name
}.collect();

Projecting into a record

If you want all fields of each array element, project into a typed record:

pub record User { name: text, age: number }

let users: list<User> = jq!{ j | .users[] }.collect();

lowers to:

SELECT jt.name, jt.age
  BULK COLLECT INTO l_users
  FROM JSON_TABLE(p_j, '$.users[*]'
    COLUMNS (name VARCHAR2(4000) PATH '$.name',
             age  NUMBER         PATH '$.age')) jt;

One JSON_TABLE column per record field, named to match.

Top-level arrays

If the source is itself an array (not an object wrapping one), use .[]:

let names: list<text> = jq!{ j | .[] | .name }.collect();

lowers to: JSON_TABLE(p_j, '$[*]' COLUMNS (v ... PATH '$.name')).

Supported jq subset

jq	Pell jq!{} support
source | .a.b.c[]	yes — nested path + iterator
source | .[]	yes — iterate top-level array
select(.f op literal)	yes — single filter stage
select(.a and .b)	yes — multiple, combined with and/or
Trailing | .field	yes — scalar projection
No trailing projection	yes — full-record projection if target is list<Record>
Comparison ops: ==, !=, <, <=, >, >=	yes
Logical: and, or	yes
Number / string / true / false / null literals	yes

Not in the subset

• Multiple iteration stages (.[].sub[]) — JSON_TABLE supports nested COLUMNS but jq’s composition is different; out of scope for v0.
• Computed projections (.a + .b, length, keys, etc.) — pell has no general jq evaluator. Project the columns you want, compute in pell.
• select() with non-comparison predicates (has, contains, test) — each would need a per-op SQL lowering. Use if in pell instead.
• .[N] indexed access — only [] / [*] are recognized.

Composes with .collect(), .one(), and for-loops

jq!{} produces a result that behaves like a sql!{} — terminate with .collect() or .one(), or use as a for-loop iterable:

let count: number = jq!{
    j | .users[] | select(.role == "admin")
}.collect().count();

for u in jq!{ j | .users[] } as User {
    log::info("{u.name}");
}

Where to go next

• Pivot — when you want to widen rows into columns (the relational counterpart to pivoting a JSON array’s fields).
• Reference: pell_re — JSON_TABLE is also what backs lots of regex-style extraction work.