How-to: Batch lookups

Every @lookupKey shape compiles around the same join: a VALUES(idx, key1, key2, …​) derived table joined against the target table on the key columns. What that join delivers depends on where the field sits, and the contract this section describes is the root field’s.

At a root field, the output list is the same length as the input key list; unmatched keys produce null at their input position. Each key is answered on its own, so nothing is deduplicated or reordered: filmById(film_id: ["1", "1", "1"]) returns three copies of film 1. The VALUES table is what makes that true, carrying one row per input position rather than a set of distinct keys. The slot-per-key shape is the join result scattered by idx, not the join result itself: idx rides the select list and the fetched rows are placed at their key’s position afterwards, which is also what carries input order, so no ORDER BY is emitted. That scatter is why a root lookup’s element type has to be nullable: a root lookup field declaring [Film!]! is rejected at build time, because one unmatched key would otherwise null the entire field. Sorts do not apply: the order is the input order, full stop.

A child @lookupKey coordinate is a different animal, and none of the paragraph above reaches it. The same VALUES join runs, but it narrows each parent’s child list rather than filling positions: there is no scatter, an unmatched key contributes no element instead of a null, and the element type stays whatever the list needs, so [Actor!]! is both correct and accepted there. The @splitQuery and class-backed-parent sections below cover those shapes.

Adding @asConnection on top of a @lookupKey field is rejected on root and child fields alike, though not by the same check, so the error text differs. A child field is rejected by the classifier with @asConnection on @lookupKey fields is invalid. A root field never reaches that check: promoting the return type to a connection makes it a generated wrapper type rather than a @table-annotated one, so @lookupKey’s root invariant rejects first, with `@lookupKey requires a @table-annotated return type. Either way the composition is unavailable; reach for @splitQuery + @asConnection when a paginated child list is what you want.

The Sakila example schema’s root lookups exercise the common shapes. The simplest is a single-scalar key:

A request filmById(film_id: ["1", "999999", "2"]) { title } returns three positions in input order: { "ACADEMY DINOSAUR" }, null, { "ACE GOLDFINGER" }. The empty-list case short-circuits before touching SQL: filmById(film_id: []) returns [] without dispatching a query.

Two @lookupKey arguments at the same call site correlate by index. customerById(customer_id: [ID] @lookupKey, store_id: ID @lookupKey) matches (customer_id[i], store_id[i]) per row; a scalar key broadcasts to every position, while two list keys must have the same length. The generator builds VALUES(idx, customer_id, store_id) JOIN customer USING (customer_id, store_id).

A request customerById(customer_id: ["1", "2", "4", "3"], store_id: "1") joins each customer id against store_id = "1" (broadcast) and returns four positions, null where a (customer_id, store_id) pair has no match. Two list arguments of unequal length are a runtime contract violation; wrap them in an input type to get framework-level enforcement.

The argument-level @lookupKey shape is right when the keys are scalar lists at the call site and the caller can be trusted to supply correlated lists. When the pairing matters and you want the schema to enforce it, the input-type shape is preferable.

An input type with @lookupKey on each leaf scalar lets the schema enforce that the keys travel together as one record. The input needs no directive of its own; its fields resolve against the consuming field’s return-type table (film_actor here). Each input element is one row of the lookup; the generator materialises VALUES(idx, film_id, actor_id) and joins on the full composite key.

The contract: each FilmActorKey is one row of the VALUES table, the join is on the full key set, and the output preserves input order. @lookupKey on the argument itself (key: [FilmActorKey!]! @lookupKey) is the carrier marker; the per-leaf @lookupKey annotations are what bind individual fields into the key tuple. A leaf without @lookupKey is excluded from the key set and resolves like any other column-bound input field.

Mutation row identification is different: @mutation’s `UPDATE/DELETE identify rows by primary-key or unique-key coverage read from the catalog, and @lookupKey on a mutation input field is no longer supported; see @mutation.

Three arms live here, and the axis that separates them is whether they are a lookup at all. Two are; the middle one only looks like one. Decode failure behaves the same on all three: a malformed or wrong-type id throws rather than dropping quietly.

Synthesised lookup-key path (@lookupKey on an [ID] arg whose target type carries @node). Each opaque base64 id decodes once per row at the arg layer to a Record<N> of the target’s primary-key columns; the generator emits VALUES(idx, pk_col1, pk_col2, …​) keyed on the decoded composite. Decode failure (a wrong-typename or malformed id) is a contract violation: it surfaces as GraphqlErrorException via CallSiteExtraction.NodeIdDecodeKeys.ThrowOnMismatch.

The output holds one slot per opaque id, in input position, null where the id decoded cleanly but matched no row; the FilmActor type’s two-column primary key (actor_id, film_id) is encoded into each id and decoded into the VALUES row.

Same-table @nodeId arg path, without @lookupKey (@nodeId(typeName: T) on an [ID] arg whose T matches the field’s return type). This one is not a lookup, despite looking like one: the decoded keys lift onto the ordinary filter rail as WHERE pk IN (…​), so there is no VALUES table, no idx, and no positional contract. It is listed here because the shape invites the lookup reading.

Three consequences follow from its being a filter, and all three differ from a lookup. Result order is whatever the query yields, not input order. An empty list narrows by nothing and returns the unfiltered table rather than []. And a malformed or wrong-type id fails the whole field with a client error rather than occupying a position: one bad element in filmsByNodeIdArg(ids: [<film_2>, "garbage"]) nulls the field and names the offending id, with no partial result. Element non-nullability is therefore fine here, which is why the example above keeps [Film!]!.

Same-table @nodeId arg path, with @lookupKey. Adding @lookupKey beside the @nodeId is the deliberate opt-in that turns the filter back into a lookup, with the VALUES join, the positional contract and the nullable-element requirement that implies:

The pairing is rejected only when the @nodeId targets a different table than the field returns (an FK-target @nodeId, where @lookupKey has nothing to key on): @lookupKey is meaningless on an FK-target @nodeId arg. For decode-side guidance on stable IDs, How-to: Global object IDs covers the encoder/decoder layout.

When a @lookupKey field hangs off a list parent and you want each parent to receive only its own filtered child list, combine @splitQuery with @lookupKey. The fetcher emits a flat SELECT joined against two VALUES tables: one for the per-parent dispatch keys (driven by the DataLoader), one for the caller-provided lookup keys.

Per request, the framework gathers every parent Film’s film_id, dispatches one batched query that joins film_actor against the per-parent key set and the caller’s actor_id list, then scatters rows back to their parents by idx. Each Film receives a list filtered by the caller’s actor keys; absence of an actor in a film yields no row (not null) at the child position, since this is a list output, not a positional one. That is why the field above declares [Actor!]!: nothing here can hold a null element, and the root lookup’s nullable-element requirement does not apply.

Without @splitQuery, the same [Actor!]! field with @lookupKey paginates across all parents in one wide SELECT: the lookup keys narrow the union of all parents' actors, not per-parent slices. That is rarely the right shape at the child level. The actors(actor_id: [Int!] @lookupKey) field on Film shows the inline (non-split) variant: it ships an inline correlated subquery per parent, useful when parent fan-out is small but wasteful at scale.

The same composition applies at deeper nesting depths through plain-object NestingField parents. Film.info.castByKey(actor_id: [Int!] @lookupKey) (info: FilmInfo, FilmInfo plain-object) treats the nested arm as just another Table-sourced lookup-keyed BatchedTableField: same DataLoader shape, deeper path.

@splitQuery + @lookupKey + @asConnection is not a valid composition. The @asConnection rejection mentioned above applies to any @lookupKey field, including @splitQuery ones; combine @splitQuery + @asConnection (covered in connections) for per-parent paginated children, or @splitQuery + @lookupKey for per-parent narrowed batches, but not all three at once.

A class-backed parent reachable from @service is implicitly DataLoader-batched on its @table-typed children (classifyChildFieldOnResultType never inspects @splitQuery on record-parent table-bound fields). That same pattern admits @lookupKey: a record-sourced lookup-keyed BatchedTableField batches keyed by the parent record’s PK and narrowed by the caller-provided lookup keys. Explicit @splitQuery is redundant on these fields, not rejected.

The same VALUES-pair join applies; the difference is purely on the parent side, where the key extraction reads from the jOOQ record’s accessor rather than from a @table parent’s projected idx column.

← How-to index