Reading and writing JSONB columns

You already have the column. Back when you built the schema, you gave webhookDeliveries a payload column typed jsonb().$type<WebhookEvent>(), a place to park the raw body a third party sends you, a shape they own and you don’t. It has been sitting there holding thousands of stored deliveries. Now the feature work arrives, and it asks two things of that column that the schema never planned for: pull the eventType field out of every delivery, and find every delivery whose payload says status: 'paid'. Neither of those is a column. Both fields live inside the JSON document.

This lesson teaches you to reach into that document: to read a single field, filter by what the JSON contains, and patch one key without rewriting the whole thing. It is also where the type system stops helping you, and that is worth knowing going in. One idea explains every example you are about to see. Drizzle types the whole column, but the moment you reach inside it you are writing raw sql, which is 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 are writing a hand-rolled SQL fragment that comes back as untyped text, which you then have to cast and re-claim yourself. Every section below follows from that.

Here is a quick refresher, since you met this column a couple of chapters ago. jsonb is the binary, indexable JSON type, the one you reach for when a column’s shape isn’t yours to fix. That is all you need to recall before we get into the read path, the write path, and the filter path.

By the end you will read whole payloads and single fields, filter by containment and key existence, and do partial writes that leave the rest of the document intact. Beyond any single operator, the skill that lasts is knowing the exact moment a JSONB field has earned promotion to a real column.

When the payload belongs in JSONB, and when it has outgrown it

You already know when to reach for jsonb: use it when the shape isn’t yours or isn’t fixed. That covers third-party webhook bodies where the sender owns the schema, audit-log details that look completely different per event type, and user-provided metadata with open-ended keys. You also know the opposite case. Skip jsonb for anything you filter or sort on across every row, or anything two different consumers have to agree about the shape of. That belongs in a real, named column with a real type.

What this lesson adds is the other half of that rule, the part you can only see once a field has been in production for a while. Call it the promotion trigger, and learn to spot it in code review, because it creeps in one commit at a time.

Here is how it unfolds. A field starts life inside jsonb, and that is correct, because nobody queries it; it is just along for the ride. Then a feature needs to filter on it, so someone adds a WHERE that reaches into the JSON. That is still fine, since it runs only occasionally. Then that filter moves onto the list view, and now it runs on every single page load, against every row, with no index able to help it. Once a JSONB path shows up in a filter that runs that often, the field has quietly become normalization debt hidden behind a raw-SQL accessor. It should have been a column three commits ago.

The fix, when you spot it, is to promote the field to a real indexed column. You don’t do that by editing the table in place, because there is already data in the JSON. You do it with an expand-then-backfill-then-contract migration: a safe staged change that adds the column, copies the values across, and only then starts depending on it. That mechanic gets its own chapter later. For now the skill is recognizing the trigger, not running the migration.

Practice making that call before you touch any operator. The following exercise gives you six concrete fields. Some belong in jsonb; some have outgrown it and should become a column. Drag each one 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 three on the “keep” side share a trait: you read them whole and never branch on them in SQL. The three on the “promote” side all ended up in a WHERE or an ORDER BY that runs on every list. That is the entire decision, and getting it right matters more than memorizing operators, because it is what keeps a schema healthy as features pile on.

Reading the whole column comes back typed

Start with the one place JSONB is completely, effortlessly typed, which is the payoff for annotating the column back in the schema. When you select the column whole, with no operator and no reaching inside, the $type<WebhookEvent> annotation rides straight through to the result. Drizzle hands you a WebhookEvent.

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

That is all it takes. No cast, no operator, no helper. deliveries[0].payload is a WebhookEvent, and your editor autocompletes its fields. The same holds through the relational query API or findFirst: anywhere the projection includes the whole payload, the type comes along.

The block below is the same select, with the parts that carry the typing marked. Hover them to see what each one does.

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

Here is the mental model the next section builds on. To TypeScript, the column is a fully-typed object the instant you select it whole, and the untyped territory begins only when you index into it inside SQL. A whole-column read sits on the typed side of that line; one operator past it and you have crossed over.

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 for you to maintain. Save the single-field accessors for when you genuinely want just one value, usually inside a WHERE.

Reaching for one field drops you into raw SQL

Now cross that line. To pull one field out of the JSON, Postgres gives you two accessor operators, and the difference between them decides how the value comes back:

• -> returns a JSON value, still jsonb. You use it to descend another level into a nested object.
• ->> returns text, a plain string. You use it to extract a leaf you are actually going to read or compare.

Drizzle has no typed builder for either of these. There is no .field('eventType') method waiting to be discovered. You write them as sql fragments, by hand, exactly the way you wrote raw filters and full-text functions in earlier lessons. This is the design, not a gap:

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

The interpolated webhookDeliveries.payload binds as a quoted column identifier, and the 'eventType' is a SQL string literal naming the key. The result is text. One reassurance holds for the whole lesson: any value you later compare this against still flows through Drizzle’s parameter binding as $1, separately from the SQL text. Reaching into JSON opens no injection hole, because the key is a literal you typed and every dynamic value is still bound rather than concatenated into the string.

Here is the part that catches people out most often. ->> always returns text, so the moment you compare its result to a number you are really doing 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 the text '100': at the first character, '9' is greater than '1'.

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

There is no error and 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`)

If you need that value back in TypeScript as a number, not just for a comparison, you cast in SQL and then re-claim the type on the TS side with sql<number>. That generic is a claim, not a check: it tells the compiler to trust that the fragment yields a number. Postgres does the actual cast; the generic only informs TypeScript so the rest of your code types correctly.

The walkthrough below takes a single query that reads a nested field, the customer’s email two levels deep, and uses an amount in the where. Step through it and it highlights three spans in turn: the operators, the cast, and the type-claim.

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, though you won’t drill it. When a leaf is buried deep, chaining ->…->…->> gets noisy. The #>> operator takes a path array and dives straight to a text leaf, so sql`${webhookDeliveries.payload}#>>'{customer,address,city}'` reads the value at the same path the chain would walk. Reach for it when the depth makes the chain hard to read; otherwise the chained form is clearer about each hop.

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 a dedicated operator you will use far more than the accessors: @> , the containment operator.

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

Read that as “rows whose payload contains this object.” It is the workhorse of JSONB querying, and it is the form a GIN index can accelerate later. Two details on it matter, and missing either one leads to a confusing debugging session.

First: the right-hand side must carry an explicit ::jsonb cast. Drizzle binds your object as a parameter, and without the cast the binding can fail at the boundary, because the driver doesn’t always know the parameter should be jsonb rather than text. The ::jsonb is not decoration and not optional; it is what tells Postgres how to treat the bound value. Write ${obj}::jsonb every time.

Second: @> tests containment, not equality. { data: { status: 'paid' } } matches a payload that has status: 'paid' along with twenty other keys beside it. That is the point: you are asking whether the payload contains this object, not whether it equals it. That makes @> ideal for partial matches against a large third-party payload, and it is also the behavior to keep in mind, since it never pins down an exact shape. There is almost always more in the document than what you matched on.

There is a narrower question you will sometimes need: not what value a key holds, but whether the key is there at all. That is the ? operator and its siblings:

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

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

The two tabs below put the two filter questions side by side on the same table. They look similar but answer completely different things.

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, no matter what else is in the document. The ::jsonb cast on the bound object is required, because without it the parameter can fail 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. The value behind it doesn’t matter, only that it is there. This is the question to ask when a key being set at all is the signal, like a timestamp that is only written once a refund happens.

A note on scale, so it does not surprise you later. An unindexed @> works fine on a few thousand rows and degrades past that, because it has to deserialize and check every row. The fix is a GIN index, which makes @> fast at any size, and it is the first thing the next chapter covers. For now, write the correct query and treat making it fast as a deliberate next step rather than something to bolt on here.

One more tool is worth recognizing today, though you won’t learn it yet. When you need to navigate inside a JSON array with a condition, such as finding any line item whose quantity exceeds ten, there is a whole SQL/JSON path language (jsonb_path_query and friends) built for exactly that. It is the right reach for predicates that walk an array. @> covers the overwhelming majority of what you will write, and the path language is there for the day it doesn’t.

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

Reading was the hard part. Writing a whole payload is straightforward: you hand Drizzle the object and it serializes it, with $type checking 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 put on every mutation this chapter: the post-write row comes back without a second query.

The interesting case is the partial update: you have a stored payload and you want to change one field without re-sending the entire document. Re-sending the whole thing is the naive approach, and it is wrong for two reasons. 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, and the 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) reaches into the object along the path array and replaces just that leaf.

The decision is mechanical: use || when the field is top-level, jsonb_set when it is nested. Both leave the rest of the document untouched, which is the whole reason you prefer them over replacing payload wholesale.

The two tabs below show each one 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 gets added, and everything already in the payload stays put. If the key already 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 the path {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 the string "premium" is quoted inside the SQL literal.

This is where the trusting read side and the raw write side meet. $type does not validate writes. A partial update written through sql can store a shape that violates WebhookEvent, and TypeScript stays perfectly silent, because you typed raw SQL rather than a checked object. Postgres accepts the bytes. Then the next typed read of that column hands the consumer a WebhookEvent that isn’t one. The type was wrong, because nothing enforced it on the way in.

So keep the two halves connected. The read side trusts $type, and that trust is only justified if the write side guarantees the shape. The thing that provides that guarantee is Zod validation at the write boundary: you validate the payload against a schema before it ever reaches a sql write, and that is what keeps the read-side $type honest. $type is a convenience that assumes a validated boundary; it is not the boundary itself. Zod gets its own chapter; for now, the point is that a raw write that skips 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: it is not binary, it can’t be indexed, 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 webhook payload

Now use the two highest-value skills together against real rows. The exercise below seeds a webhook_deliveries table with a handful of deliveries, each carrying a realistic webhook body in its payload column: an eventType, a nested data object with a status and an amount, and a refundedAt key on some rows but not others.

Your task is to return the deliveries that are both paid and over a threshold amount, selecting the eventType of each. Two pitfalls are in play, and they are exactly the two this lesson warned you about. The amount filter has to cast, because one row has an amount that is numerically below the threshold but lexically above it, so a string comparison would wrongly include it. The status match wants @> containment with its ::jsonb cast, though an equivalent ->> path works too. Get both right and the correct rows come back.

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 correct set is rows 1, 4, and 6, the three deliveries that are paid and over 500. Both pitfalls show up in that seed. Row 2 is paid but its amount is 90, so if you filter 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 in the table but aren’t status: 'paid', so the containment filter has to drop them. Get the @> containment right, with its ::jsonb cast, and add the numeric cast, and exactly those three invoice.paid rows come back.

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 @> containment filter keeps only payloads that nest status: 'paid' under data, and its right-hand side carries the required ::jsonb cast. The amount path descends with -> into data, lands on amount as text with ->>, then casts to ::numeric before the comparison. Without that cast, > 500 would be the lexical string comparison the lesson warned about. An equivalent payload->'data'->>'status' = 'paid' works in place of the @> form.

Note

The @> filter in that solution is correct, but on a real table it gets slow once the row count climbs, because it scans every payload. The fix is a GIN index on the jsonb column, and that is the first thing you will reach for in the next chapter on performance and integrity. The query you just wrote doesn’t change; you only add the index behind it.

External resources

These four references are worth a bookmark. Drizzle’s docs on its sql magic operators are the canonical answer for everything the typed builder doesn’t cover, and JSONB is one case of that broader drop-to-raw-SQL approach. Its column-types page is where the $type annotation that makes the whole-column read typed is spelled out. The Postgres docs on JSON functions and operators are the full catalog: every accessor, @>, 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.