Reading and writing JSONB columns

You already have the column. When you built the schema you gave webhookDeliveries a payload typed jsonb().$type<WebhookEvent>(), a place to park the raw body a third party sends you. Now a feature asks two things of it that no column was made for: pull the eventType field out of every delivery, and find every delivery whose payload says status: 'paid'. Both fields live inside the JSON document, not in a column of their own.

This lesson teaches you to reach into that document: read a single field, filter by what the JSON contains, and patch one key without rewriting the rest. One idea explains every example. Drizzle types the whole column, but the moment you reach inside it you are writing raw sql, typed at the boundary and untyped in the middle. Read the column whole and TypeScript hands you a WebhookEvent; reach for one field and you get back untyped text that you cast and re-claim yourself.

jsonb is the binary, indexable JSON type, the one you reach for when a column’s shape isn’t yours to fix. Along the way you will also learn to spot the moment a field inside it has earned promotion to a real column.

When to keep a field in JSONB and when to promote it

Reach for jsonb when the shape isn’t yours or isn’t fixed: third-party webhook bodies, audit-log details that differ per event type, user metadata with open-ended keys. Skip it for anything you filter or sort on across every row, or anything two consumers must agree on the shape of. That belongs in a named column with a real type.

This lesson adds the other half of that rule, the promotion trigger, which only shows up after a field has been in production for a while.

It creeps in one commit at a time. A field starts inside jsonb, correctly, because nobody queries it. Then a feature filters on it, so someone adds a WHERE that reaches into the JSON; still fine, since it runs only occasionally. Then that filter moves onto the list view, where it runs on every page load, against every row, with no index to help. At that point the field has become normalization debt hidden behind a raw-SQL accessor. It should have been a column three commits ago.

The fix is to promote the field to a real indexed column, using a staged expand-backfill-contract migration that gets its own chapter later. For now the skill is recognizing the trigger, not running the migration.

The following exercise gives you six fields. Drag each to where it belongs. The tell is always the same question: do you only read this field, or do you query on it across all rows?

Sort each field by what you do with it: keep it in `jsonb` if you only ever read it back, promote it to a real column if you filter, sort, or join on it across all rows. Drag each item into the bucket it belongs to, then press Check.

Keep in `jsonb` Read it back, never query across rows

Promote to a column Filtered, sorted, or joined on every list

The raw webhook body, stored exactly as the sender sent it

An audit-log details blob whose keys differ per event type

Open-keyed user metadata where every tenant adds their own fields

The status you filter every invoice list by

A priority you let users sort the list on

The eventType you now branch on in every query

Check Reset

The “keep” three you read whole and never branch on in SQL; the “promote” three each ended up in a WHERE or ORDER BY that runs on every list.

Selecting the whole column returns a typed value

Selecting the column whole, with no operator and no reaching inside, carries the $type<WebhookEvent> annotation straight through to the result. Drizzle hands you a WebhookEvent, no cast and no helper, and your editor autocompletes its fields.

const deliveries = await db
  .select({ payload: webhookDeliveries.payload })
  .from(webhookDeliveries);

deliveries[0].payload is a WebhookEvent. The same holds through the relational query API or findFirst: anywhere the projection includes the whole payload, the type comes along.

Hover the marked parts below to see what each one contributes to the typing.

const deliveries = await db
  .select({ payload: webhookDeliveries.payload })
  .from(webhookDeliveries);

A whole-column read sits on the typed side of a line; the untyped territory begins only when you index into the JSON inside SQL, which the next section covers.

Tip

When the code consuming a delivery needs more than one field off the payload, reach for the whole-column read. It comes back typed, it autocompletes, and it is one fewer raw sql fragment to maintain. Save the single-field accessors for when you want just one value, usually inside a WHERE.

Reading one field drops to hand-written SQL

To pull one field out of the JSON, Postgres gives you two accessor operators, and the choice between them sets how the value comes back:

• -> returns jsonb. Use it to descend another level into a nested object.
• ->> returns text. Use it on the leaf you actually want to read or compare.

Drizzle has no typed builder for either. You write them as sql fragments by hand, the same way you wrote raw filters and full-text functions in earlier lessons:

sql`${webhookDeliveries.payload}->>'eventType'`

Here webhookDeliveries.payload binds as a quoted column identifier and 'eventType' is a SQL string literal naming the key; the result is text. This opens no injection hole: any value you later compare against still binds as $1, separate from the SQL text.

Here is where people get it wrong. ->> always returns text, so comparing its result to a number is really a string comparison, and strings compare lexically, character by character. The filter below silently returns deliveries with an amount of 90, because the text '90' sorts after '100': at the first character, '9' beats '1'.

where(sql`${webhookDeliveries.payload}->>'amount' > '100'`)

No error, no warning, just wrong rows. The fix is to cast the extracted text to a real number in the SQL, before the comparison runs.

where(sql`(${webhookDeliveries.payload}->>'amount')::numeric > 100`)

To get that value back in TypeScript as a number, cast in SQL and re-claim the type with sql<number>. That generic is a claim, not a check: Postgres does the actual cast, and the generic only tells the compiler to trust that the fragment yields a number.

The walkthrough below steps through one query that reads a nested field, the customer’s email two levels deep, and uses an amount in the where, highlighting the operators, the cast, and the type-claim in turn.

const bigPaidDeliveries = await db
  .select({
    email: sql<string>`${webhookDeliveries.payload}->'customer'->>'email'`,
  })
  .from(webhookDeliveries)
  .where(sql`(${webhookDeliveries.payload}->'data'->>'amount')::numeric > 500`);

-> descends one level and keeps the result as jsonb, so you can chain another accessor onto it. Here it steps into the nested customer object, and on the filter line into data. As long as you're still descending, stay on ->.

const bigPaidDeliveries = await db
  .select({
    email: sql<string>`${webhookDeliveries.payload}->'customer'->>'email'`,
  })
  .from(webhookDeliveries)
  .where(sql`(${webhookDeliveries.payload}->'data'->>'amount')::numeric > 500`);

->> is how you land. It pulls the leaf out as text. The last hop of any path you actually read is ->>, never -> — -> would hand you a JSON-wrapped string, quotes and all.

const bigPaidDeliveries = await db
  .select({
    email: sql<string>`${webhookDeliveries.payload}->'customer'->>'email'`,
  })
  .from(webhookDeliveries)
  .where(sql`(${webhookDeliveries.payload}->'data'->>'amount')::numeric > 500`);

The cast that makes the comparison real. Without ::numeric, ->>'amount' is text and > 500 becomes a lexical string comparison where '90' beats '500'. This one token is the difference between a correct filter and a silent bug.

const bigPaidDeliveries = await db
  .select({
    email: sql<string>`${webhookDeliveries.payload}->'customer'->>'email'`,
  })
  .from(webhookDeliveries)
  .where(sql`(${webhookDeliveries.payload}->'data'->>'amount')::numeric > 500`);

The TS-side re-claim. The builder lost the type the moment you dropped into a raw sql fragment, so sql<string> tells the compiler what comes back. It's a claim you're making, not something TypeScript verifies — Postgres guarantees the runtime type, this only informs the editor.

← Previous 1 / 1 Next →

One more form is worth recognizing: when a leaf is buried deep, #>> takes a path array and dives straight to a text leaf, so sql`${webhookDeliveries.payload}#>>'{customer,address,city}'` reaches the same value a chain of ->…->> would walk to.

Filtering by what the payload contains

Extracting a field is one job; finding rows by what their JSON contains is the other, and it has its own operator: @> , the containment operator.

where(sql`${webhookDeliveries.payload} @> ${{ data: { status: 'paid' } }}::jsonb`)

Read that as “rows whose payload contains this object.” Two details matter, and missing either leads to a confusing debugging session.

The right-hand side needs an explicit ::jsonb cast. Drizzle binds your object as a parameter, and without the cast the driver may treat it as text, not jsonb, and the binding fails. Write ${obj}::jsonb every time.

@> tests containment, not equality. { data: { status: 'paid' } } matches a payload that has status: 'paid' alongside any number of other keys. That makes it ideal for partial matches against a large third-party payload, but it never pins down an exact shape: there is almost always more in the document than what you matched on.

Sometimes the question is narrower: not what value a key holds, but whether the key is present at all. That is the ? operator and its siblings:

where(sql`${webhookDeliveries.payload} ? 'refundedAt'`)

That reads “rows where the refundedAt key is set,” whatever value sits behind it. ?| matches if any of several keys exist; ?& if all of them do. You reach for these far less than @>, but they are the right tool when the real question is whether a field was ever populated.

The two tabs below run both filter questions against the same table.

Contains a value (@>)

const paid = await db
  .select()
  .from(webhookDeliveries)
  .where(sql`${webhookDeliveries.payload} @> ${{ data: { status: 'paid' } }}::jsonb`);

Match on value. Returns every delivery whose payload contains status: 'paid' nested under data. The ::jsonb cast is required, or the parameter fails to bind. This is containment, not equality: a payload with that status plus a hundred other keys still matches.

Has a key (?)

const refunded = await db
  .select()
  .from(webhookDeliveries)
  .where(sql`${webhookDeliveries.payload} ? 'refundedAt'`);

Match on presence. Returns every delivery where the refundedAt key exists at the top level, whatever its value. Ask this when a key being set at all is the signal, like a timestamp written only once a refund happens.

One note on scale: an unindexed @> checks every row and slows down past a few thousand of them. The fix is a GIN index, the first thing the next chapter covers.

Worth recognizing today, though not learning yet: to test a condition inside a JSON array, such as any line item with a quantity over ten, Postgres has a SQL/JSON path language (jsonb_path_query and friends) built for exactly that.

Watch video

Supabase walks the same accessors and filters in raw Postgres in five minutes: -> vs ->>, nested access, and the ?, ?&, ?|, and @> operators you just met.

Writing and partially updating JSONB

Writing a whole payload is straightforward: hand Drizzle the object, it serializes it, and $type checks the shape at compile time.

const [created] = await db
  .insert(webhookDeliveries)
  .values({ deliveryId, payload: { eventType: 'invoice.paid', data: { status: 'paid' } } })
  .returning();

That is the same .returning() tail you have used on every mutation this chapter: the written row comes back without a second query.

The interesting case is the partial update: change one field of a stored payload without re-sending the whole document. Re-sending it all is the naive approach, and it is wrong twice over. You would have to read the current value first, and you would overwrite any concurrent edit that landed between your read and your write. Postgres gives you two precise tools instead, and which one you reach for depends only on how deep the field sits.

• || is a shallow merge. payload || '{...}'::jsonb adds or overwrites keys at the top level, last value wins. Reach for it to set a top-level field.
• jsonb_set writes one nested path. jsonb_set(payload, '{customer,tier}', '"premium"'::jsonb) follows the path array into the object and replaces just that leaf.

The rule is mechanical: || for a top-level field, jsonb_set for a nested one. Both leave the rest of the document untouched, which is why you prefer them over replacing payload wholesale.

The two tabs below show each as a before and after, with the existing payload included so you can see exactly what changed.

Merge a field (||)

// before: { eventType: 'invoice.paid', data: { status: 'paid' } }
await db
  .update(webhookDeliveries)
  .set({ payload: sql`${webhookDeliveries.payload} || ${{ processedAt: '2026-06-05T10:00:00Z' }}::jsonb` })
  .where(eq(webhookDeliveries.deliveryId, deliveryId));
// after: { eventType: 'invoice.paid', data: { status: 'paid' }, processedAt: '…' }

For a top-level field. || merges the right object into the left at the top level: processedAt is added, everything already there stays, and if the key existed the right side wins. Note the ::jsonb cast on the merged object, same as @>.

Set a nested path (jsonb_set)

// before: { eventType: 'invoice.paid', data: { status: 'paid' }, customer: { tier: 'free' } }
await db
  .update(webhookDeliveries)
  .set({ payload: sql`jsonb_set(${webhookDeliveries.payload}, '{customer,tier}', '"premium"'::jsonb)` })
  .where(eq(webhookDeliveries.deliveryId, deliveryId));
// after: { …, customer: { tier: 'premium' } }

For a nested field. jsonb_set(target, path, new_value) rewrites just the leaf at {customer,tier} and leaves the rest of customer, and the rest of the payload, intact. The path is a text-array literal and the new value is jsonb, so "premium" is quoted inside the SQL literal.

This is where the trusting read side meets the raw write side. $type does not validate writes. A partial update written through sql can store a shape that violates WebhookEvent, and TypeScript stays silent, because you typed raw SQL rather than a checked object. Postgres accepts the bytes, and the next typed read hands the consumer a WebhookEvent that isn’t one. $type is a convenience that assumes a validated boundary; it is not the boundary itself. What provides the boundary is Zod validation at the write boundary: validate the payload against a schema before it reaches a sql write, and the read-side $type stays honest. Zod gets its own chapter; for now, the point is that a raw write skipping validation is how a typed column starts handing back the wrong shape.

Caution

None of this works on a plain json column, and you should never reach for one. json stores the document as raw text: not binary, not indexable, and the operators behave differently or not at all. The course is jsonb-only, end to end. If you ever see json() in a schema, treat it as a bug to fix.

Practice: query a JSONB payload

This exercise seeds a webhook_deliveries table whose payload column holds a realistic webhook body: an eventType, a nested data object with a status and amount, and a refundedAt key on some rows. Return the eventType of every delivery that is paid and has an amount over 500, exercising both pitfalls this lesson covered: @> containment for the nested status (with its ::jsonb cast), and a ::numeric cast before the amount comparison.

Return the eventType of every delivery whose payload has status: 'paid' (nested under data) AND whose data.amount is greater than 500. Mind the cast — amount comes out as JSON text until you make it a number. Alias the extracted value as event_type and order by id.

View schema & data

CREATE TABLE webhook_deliveries (
  id int PRIMARY KEY,
  delivery_id text NOT NULL UNIQUE,
  payload jsonb NOT NULL,
  received_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO webhook_deliveries (id, delivery_id, payload) VALUES
  (1, 'evt_a', '{"eventType":"invoice.paid","data":{"status":"paid","amount":900}}'),
  (2, 'evt_b', '{"eventType":"invoice.paid","data":{"status":"paid","amount":90}}'),
  (3, 'evt_c', '{"eventType":"invoice.sent","data":{"status":"open","amount":1200}}'),
  (4, 'evt_d', '{"eventType":"invoice.paid","data":{"status":"paid","amount":750},"refundedAt":"2026-06-01T00:00:00Z"}'),
  (5, 'evt_e', '{"eventType":"invoice.paid","data":{"status":"open","amount":2000}}'),
  (6, 'evt_f', '{"eventType":"invoice.paid","data":{"status":"paid","amount":600}}');

Run query Feedback Reset

The answer is rows 1, 4, and 6. The other rows are the two traps. Row 2 is paid but its amount is 90; without the ::numeric cast, the text '90' sorts after '500' and the lexical comparison wrongly lets it in. Rows 3 and 5 carry the highest amounts but aren’t status: 'paid', so the containment filter must drop them.

Reference solution

SELECT payload->>'eventType' AS event_type
FROM webhook_deliveries
WHERE payload @> '{"data":{"status":"paid"}}'::jsonb
  AND (payload->'data'->>'amount')::numeric > 500
ORDER BY id;

The @> filter keeps only payloads that nest status: 'paid' under data. The amount path descends with -> into data, reads amount as text with ->>, then casts to ::numeric so > 500 is a numeric comparison, not a lexical one. An equivalent payload->'data'->>'status' = 'paid' works in place of the @> form.

Note

On a large table this @> filter scans every payload and slows down. The fix is a GIN index on the jsonb column, which the next chapter on performance and integrity reaches for first; the query stays the same.

External resources

Drizzle’s sql magic-operator docs cover everything the typed builder doesn’t, JSONB included, and its column-types page documents the $type annotation that makes the whole-column read typed. The Postgres JSON functions reference is the full catalog of accessors, @>, jsonb_set, ||, and the SQL/JSON path language. The practical guide ties them together with runnable examples and the indexing payoff.

Drizzle ORM — Magic sql operator

orm.drizzle.team

The sql`` template, parameterization, and how to write what the builder doesn't expose — including JSONB operators.

Drizzle ORM — jsonb column and $type

orm.drizzle.team

The jsonb() column declaration and the $type<…> annotation that carries a typed shape through a whole-column read — compile-time only, no runtime check.

PostgreSQL — JSON functions and operators

postgresql.org

The full reference for ->, ->>, #>>, @>, ?, ||, jsonb_set, and the SQL/JSON path language.

A practical guide to the JSONB type in Postgres

wirekat.com

A hands-on walkthrough of containment, accessors, jsonb_set, key removal, and the GIN index that makes @> fast at scale.