JSON Columns in Drizzle ORM: jsonb, Queries, and Type Safety

Q: How do I define a JSON column in Drizzle ORM?

Use json("column_name") for PostgreSQL json or MySQL json columns, or jsonb("column_name") for PostgreSQL jsonb (the preferred binary format). Both functions come from "drizzle-orm/pg-core" (PostgreSQL) or "drizzle-orm/mysql-core" (MySQL). To add TypeScript types, chain .$type () — for example: jsonb("metadata").$type (). This gives compile-time type checking on reads and writes: if you insert an object that doesn't match Metadata, TypeScript flags it. Without .$type<>(), Drizzle infers the column type as unknown, which still works at runtime but loses compile-time safety. The column value is automatically serialized to a JSON string on write and parsed back to a JavaScript value on read — you never call JSON.stringify or JSON.parse manually.

Q: How do I query a JSON field value in Drizzle ORM?

Use the sql tagged template literal for JSON path queries, since Drizzle does not have a built-in JSON path operator helper. For PostgreSQL, use the ->> operator to extract a text value: sql`${table.metadata}->>"$.key"` or the standard PostgreSQL syntax sql`${table.metadata}->>'key'`. For MySQL, use JSON_EXTRACT: sql`JSON_EXTRACT(${table.metadata}, '$.key')`. You can use these sql expressions in .where() clauses, .orderBy(), and .select() columns. Example: db.select().from(users).where(sql`${users.metadata}->>"$.plan" = 'pro'`). For containment queries in PostgreSQL jsonb, use the @> operator: sql`${table.metadata} @> '{"active":true}'::jsonb`.

Q: What is the difference between json() and jsonb() in Drizzle?

json() maps to the PostgreSQL json type, which stores the text representation verbatim and re-parses it on every read. jsonb() maps to PostgreSQL jsonb, which stores a binary-decomposed form that is faster to query, supports GIN indexing for containment and existence operators, and eliminates key duplication and insignificant whitespace. For most new applications, jsonb() is the right choice — it is 10–30% faster for path operator queries and is the only type that supports GIN indexes. The json() type preserves key order and insignificant whitespace, which is occasionally useful when you need byte-for-byte fidelity. In Drizzle, both are imported from "drizzle-orm/pg-core" and share the same .$type<>() generic API. For MySQL, only json() is available (MySQL has a single JSON type with its own binary storage).

Q: How do I add TypeScript types to a JSON column in Drizzle?

Chain .$type () on the json() or jsonb() call in your table definition. T can be any TypeScript interface, type alias, or union. Example: export const usersTable = pgTable("users", { metadata: jsonb("metadata").$type () }). After this, db.select().from(usersTable) returns rows where metadata has type { plan: string; features: string[] } | null (null if the column is nullable). When you insert, Drizzle checks at compile time that the value matches T. This type is erased at runtime — there is no runtime validation. For runtime safety, use drizzle-zod to generate a Zod schema from the table: createSelectSchema(usersTable) produces a Zod schema where the metadata field uses z.unknown() by default, which you can override with .extend({ metadata: metadataSchema }).

Q: How do I use Drizzle with Zod for JSON validation?

Install drizzle-zod: npm install drizzle-zod zod. Then import createSelectSchema and createInsertSchema from "drizzle-zod". createSelectSchema(table) generates a Zod schema for rows read from the database — all columns, including json/jsonb columns, are included with types inferred from .$type<>(). createInsertSchema(table) generates a schema for insert values, with required and optional fields matching Drizzle's insert inference. For JSON columns, the generated schema defaults to z.unknown() unless you add .$type () to the column, in which case drizzle-zod maps T to its closest Zod equivalent. You can refine the schema with .extend({ metadata: myMetadataZodSchema }) for stricter runtime validation. Parse query results with schema.safeParse(row) before using the data in application logic.

Q: How do I index a JSON column in Drizzle ORM?

For PostgreSQL jsonb columns, define a GIN index in the table's third argument using index().using("gin").on(table.column). Example: export const usersTable = pgTable("users", { metadata: jsonb("metadata").$type () }, (table) => [index("idx_users_metadata_gin").using("gin").on(table.metadata)]). Drizzle generates CREATE INDEX idx_users_metadata_gin ON users USING gin(metadata) in the migration. A GIN index accelerates @> containment queries ("find rows where metadata contains {active:true}") and the ? existence operator. For a partial GIN index on a specific path, use a SQL expression index via sql`(metadata->'tags')` passed to the .on() call. For json (not jsonb) columns, GIN indexing is not supported — this is a PostgreSQL limitation, not a Drizzle limitation.

Written and reviewed by the Jsonic editorial team — every guide is verified against the official spec or runtime before publication.

Last updated: May 27, 2026

Drizzle ORM defines JSON columns with json('col') or jsonb('col') and infers TypeScript types from .$type<MyInterface>(). Unlike raw SQL, Drizzle's type system catches JSON shape mismatches at compile time. For PostgreSQL JSON path queries, use the sql tagged template literal: sql`${table.col}->>'key'`. Drizzle supports both PostgreSQL jsonb (indexed, faster) and MySQL json columns with the same API. For runtime validation of data read from the database, pair Drizzle with Zod via drizzle-zod. This guide covers 5 topics: defining JSON columns, inserting and selecting typed JSON, querying with JSON path operators, custom JSON transformations, and Zod integration for runtime validation.

Define JSON Columns in Drizzle

Bottom line: use jsonb('col') for PostgreSQL (binary, indexable, faster) and json('col') for MySQL. Chain .$type<T>() to give the column a compile-time TypeScript type. Without .$type<>(), reads return unknown.

Both json and jsonb are imported from "drizzle-orm/pg-core" for PostgreSQL, or json alone from "drizzle-orm/mysql-core" for MySQL (MySQL has only one JSON type). The .$type<T>() generic is a pure TypeScript overlay — it does not affect the generated SQL or the database schema. Drizzle automatically calls JSON.stringify on write and JSON.parse on read; you never handle serialization manually. If you need a default value, chain .default() or .notNull() as with any other column type.

import { pgTable, serial, text, jsonb, timestamp } from "drizzle-orm/pg-core"
import { mysqlTable, int, varchar, json } from "drizzle-orm/mysql-core"

// --- TypeScript interfaces for the JSON columns ---
interface UserMetadata {
  plan: "free" | "pro" | "enterprise"
  features: string[]
  createdAt: string
}

interface OrderLineItem {
  productId: number
  quantity: number
  price: number
}

// --- PostgreSQL: use jsonb for indexed, binary-stored JSON ---
export const usersTable = pgTable("users", {
  id:       serial("id").primaryKey(),
  email:    text("email").notNull().unique(),
  // jsonb with TypeScript type — reads return UserMetadata | null
  metadata: jsonb("metadata").$type<UserMetadata>(),
})

// --- PostgreSQL: array-of-objects in jsonb ---
export const ordersTable = pgTable("orders", {
  id:        serial("id").primaryKey(),
  userId:    int("user_id").notNull(),
  lineItems: jsonb("line_items").$type<OrderLineItem[]>().notNull(),
  createdAt: timestamp("created_at").defaultNow(),
})

// --- MySQL: json() (MySQL has a single JSON type) ---
export const productsTable = mysqlTable("products", {
  id:         int("id").primaryKey().autoincrement(),
  name:       varchar("name", { length: 255 }).notNull(),
  attributes: json("attributes").$type<Record<string, string>>(),
})

// --- json() without .$type<>() — reads return unknown ---
export const logsTable = pgTable("logs", {
  id:      serial("id").primaryKey(),
  payload: jsonb("payload"),   // type is unknown — consider adding .$type<>()
})

Insert and Select Typed JSON

Bottom line: insert a plain JavaScript object — Drizzle serializes it. Select returns the typed object from .$type<T>() with no extra parsing step needed.

When you call db.insert(table).values({...}), the TypeScript compiler checks the JSON column value against T from .$type<T>() at compile time. At runtime, Drizzle calls JSON.stringify and sends the result to the database driver. On select, the driver returns a string which Drizzle parses back to an object — so the value you receive is a fully typed object matchingT. You can filter in a .where() clause on the column's top-level value, but for nested path queries you need the sql tagged template (covered in the next section).

import { db } from "./db"   // your Drizzle client
import { usersTable, ordersTable } from "./schema"
import { eq } from "drizzle-orm"

// --- Insert: TypeScript checks the metadata shape ---
await db.insert(usersTable).values({
  email: "alice@example.com",
  metadata: {
    plan: "pro",
    features: ["analytics", "api-access"],
    createdAt: new Date().toISOString(),
  },
})

// --- Select all columns: metadata is typed as UserMetadata | null ---
const users = await db.select().from(usersTable)
// users[0].metadata?.plan  →  "pro"

// --- Select specific columns ---
const emails = await db
  .select({ email: usersTable.email, metadata: usersTable.metadata })
  .from(usersTable)

// --- Where on a top-level typed value (not a JSON path) ---
// This works if the entire column equals a JSON value, but path filtering
// requires the sql operator (see next section)

// --- Insert array-of-objects into jsonb ---
await db.insert(ordersTable).values({
  userId: 1,
  lineItems: [
    { productId: 42, quantity: 2, price: 9.99 },
    { productId: 55, quantity: 1, price: 24.50 },
  ],
})

// --- Read back typed array ---
const [order] = await db
  .select()
  .from(ordersTable)
  .where(eq(ordersTable.id, 1))

const total = order.lineItems.reduce(
  (sum, item) => sum + item.price * item.quantity,
  0
)
console.log("Order total:", total.toFixed(2))

Query JSON with Path Operators

Bottom line: Drizzle passes sql tagged template expressions through to the database as raw SQL. Use sql`${table.col}->>'key'` for PostgreSQL text extraction andsql`JSON_EXTRACT(${table.col}, '$.key')` for MySQL.

PostgreSQL has two JSON operators: -> extracts a value as a JSON type, and ->> extracts it as text (suitable for .where() string comparisons). For deeply nested keys, chain operators: table.col->'outer'->>'inner'. The @> containment operator checks if the left jsonb value contains the right — useful for querying by a subset of JSON. MySQL uses JSON_EXTRACT(col, '$.key') or the shorthand col->'$.key'. Wrap any sql expression in eq(), gt(), or other Drizzle operators, or use it directly in .where() as a boolean expression.

import { db } from "./db"
import { usersTable, ordersTable } from "./schema"
import { sql, eq, and, gt } from "drizzle-orm"

// --- PostgreSQL: ->> extracts a JSON field as text ---
const proUsers = await db
  .select()
  .from(usersTable)
  .where(sql`${usersTable.metadata}->>'plan' = 'pro'`)

// --- PostgreSQL: nested path ---
const withFeature = await db
  .select()
  .from(usersTable)
  .where(sql`${usersTable.metadata}->'features' ? 'api-access'`)

// --- PostgreSQL: @> containment (jsonb only) ---
// Finds rows where metadata contains {"plan":"enterprise"}
const enterprise = await db
  .select()
  .from(usersTable)
  .where(sql`${usersTable.metadata} @> '{"plan":"enterprise"}'::jsonb`)

// --- Combine with Drizzle operators ---
const recent = await db
  .select()
  .from(usersTable)
  .where(
    and(
      sql`${usersTable.metadata}->>'plan' != 'free'`,
      gt(usersTable.id, 100)
    )
  )

// --- Extract a JSON field as a select column ---
const plans = await db
  .select({
    email: usersTable.email,
    plan: sql<string>`${usersTable.metadata}->>'plan'`,
  })
  .from(usersTable)
// plans[0].plan is typed as string because of sql<string>

// --- MySQL: JSON_EXTRACT ---
// import { productsTable } from "./schema"
// const red = await db
//   .select()
//   .from(productsTable)
//   .where(sql`JSON_EXTRACT(${productsTable.attributes}, '$.color') = 'red'`)

Update Nested JSON Fields

Bottom line: Drizzle has no built-in partial JSON update helper. The safest approach is a full-document replace: read the row, merge in JavaScript, write it back. For server-side partial updates without a round-trip, use PostgreSQL's jsonb_set() or MySQL's JSON_SET() via thesql operator.

Full-document replace is simple and works across all databases, but requires a read-then-write round-trip. For high-concurrency updates, add an optimistic concurrency check: read a version column, increment it on write, and add a .where(eq(table.version, readVersion)) clause. If the update affects 0 rows, another process updated first — retry. For server-side updates, jsonb_set(col, '{path}', value) lets PostgreSQL merge the change atomically. Pass a JSON path array as the second argument and a ::jsonb-cast value as the third.

import { db } from "./db"
import { usersTable } from "./schema"
import { eq, sql } from "drizzle-orm"

// --- Full-document replace (safest, any database) ---
async function upgradePlan(userId: number, newPlan: "pro" | "enterprise") {
  const [user] = await db
    .select()
    .from(usersTable)
    .where(eq(usersTable.id, userId))

  if (!user || !user.metadata) throw new Error("User not found")

  await db
    .update(usersTable)
    .set({ metadata: { ...user.metadata, plan: newPlan } })
    .where(eq(usersTable.id, userId))
}

// --- PostgreSQL jsonb_set: atomic server-side update ---
// jsonb_set(target, path, new_value, create_if_missing)
await db
  .update(usersTable)
  .set({
    metadata: sql`jsonb_set(${usersTable.metadata}, '{plan}', '"enterprise"'::jsonb, true)`,
  })
  .where(eq(usersTable.id, 42))

// --- Append to a jsonb array field without reading first ---
await db
  .update(usersTable)
  .set({
    metadata: sql`
      jsonb_set(
        ${usersTable.metadata},
        '{features}',
        (${usersTable.metadata}->'features') || '["sso"]'::jsonb
      )
    `,
  })
  .where(eq(usersTable.id, 42))

// --- Optimistic concurrency with version column ---
// Add: version: integer("version").notNull().default(0)  to the table schema
// Then on update:
// const result = await db
//   .update(usersTable)
//   .set({ metadata: newMetadata, version: sql`version + 1` })
//   .where(and(eq(usersTable.id, userId), eq(usersTable.version, readVersion)))
// if (result.rowCount === 0) throw new Error("Concurrent update detected")

Drizzle-Zod for Runtime Validation

Bottom line: drizzle-zod generates Zod schemas directly from Drizzle table definitions. Use createSelectSchema(table) to validate rows returned by queries, and createInsertSchema(table) to validate request bodies before inserting.

Install with npm install drizzle-zod zod. The generated schemas derive column types from Drizzle's TypeScript inference — including .$type<T>() for JSON columns. For JSON columns, the generated Zod type is z.unknown() unless you extend the schema. Override with .extend({ metadata: metadataZodSchema }) to get full runtime shape validation. Use.partial() on the insert schema to make all fields optional for PATCH endpoints. ThesafeParse result surfaces field-level errors you can return directly as API validation responses.

import { createSelectSchema, createInsertSchema } from "drizzle-zod"
import { z } from "zod"
import { usersTable } from "./schema"
import { db } from "./db"

// --- Zod schema for UserMetadata ---
const userMetadataSchema = z.object({
  plan: z.enum(["free", "pro", "enterprise"]),
  features: z.array(z.string()),
  createdAt: z.string().datetime(),
})

// --- createSelectSchema: for rows read from the DB ---
const selectUserSchema = createSelectSchema(usersTable).extend({
  metadata: userMetadataSchema.nullable(),   // override the json column
})

// --- createInsertSchema: for validated insert payloads ---
const insertUserSchema = createInsertSchema(usersTable).extend({
  metadata: userMetadataSchema.optional(),
})

// --- Runtime-validate a query result ---
async function getUserById(id: number) {
  const rows = await db.select().from(usersTable)
  const result = selectUserSchema.safeParse(rows[0])

  if (!result.success) {
    console.error("DB row failed validation:", result.error.flatten())
    throw new Error("Data integrity error")
  }

  return result.data   // fully typed and runtime-validated
}

// --- Validate an API request body before inserting ---
async function createUser(rawBody: unknown) {
  const result = insertUserSchema.safeParse(rawBody)
  if (!result.success) {
    return { error: result.error.flatten().fieldErrors }
  }

  await db.insert(usersTable).values(result.data)
  return { success: true }
}

// --- Infer the TypeScript type from the Zod schema ---
type SelectUser = z.infer<typeof selectUserSchema>
//    SelectUser.metadata  →  UserMetadata | null  (runtime-validated)

GIN Indexes for JSON Search

Bottom line: define a GIN index on a jsonb column in Drizzle's third table argument using index('name').using('gin').on(table.col). The migration Drizzle generates adds aUSING gin clause. GIN indexes accelerate the @> containment and ? existence operators — without one, PostgreSQL scans every row.

GIN (Generalized Inverted Index) decomposes each jsonb value into its key-value pairs and builds an inverted index mapping each JSON element to the rows that contain it. This makes @> containment queries O(log n) instead of O(n). For a table with millions of rows and jsonb columns with 10–50 keys, a GIN index can reduce query time from seconds to milliseconds. GIN indexes have higher write overhead than B-tree indexes — they're best for columns that are read-heavy and updated less frequently. For expression indexes on a specific JSON path (e.g. index only the plan field), use a B-tree index with asql expression.

import { pgTable, serial, text, jsonb, integer, index } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"

interface Metadata {
  plan: string
  features: string[]
  tags: string[]
}

// --- GIN index on the full jsonb column ---
export const usersTable = pgTable(
  "users",
  {
    id:       serial("id").primaryKey(),
    email:    text("email").notNull().unique(),
    metadata: jsonb("metadata").$type<Metadata>().notNull(),
  },
  (table) => [
    index("idx_users_metadata_gin").using("gin").on(table.metadata),
  ]
)

// Generated migration SQL:
// CREATE INDEX idx_users_metadata_gin ON users USING gin(metadata);

// --- B-tree expression index on a specific JSON path ---
// (faster for equality queries on a single field, no GIN overhead)
export const usersWithPlanIndex = pgTable(
  "users_plan_idx_example",
  {
    id:       serial("id").primaryKey(),
    metadata: jsonb("metadata").$type<Metadata>().notNull(),
  },
  (table) => [
    // B-tree index on the extracted "plan" text field
    index("idx_users_plan").on(sql`(${table.metadata}->>'plan')`),
  ]
)

// --- Using the GIN index: @> containment query ---
import { db } from "./db"

// This query hits the GIN index:
const proUsers = await db
  .select()
  .from(usersTable)
  .where(sql`${usersTable.metadata} @> '{"plan":"pro"}'::jsonb`)

// Existence operator ? (does the key exist?) also uses GIN:
const withTags = await db
  .select()
  .from(usersTable)
  .where(sql`${usersTable.metadata} ? 'tags'`)

// jsonb_path_exists for JSONPath (PostgreSQL 12+):
const hasApiAccess = await db
  .select()
  .from(usersTable)
  .where(sql`jsonpath_exists(${usersTable.metadata}, '$.features[*] ? (@ == "api-access")')`)

FAQ

How do I define a JSON column in Drizzle ORM?

Use jsonb("col") for PostgreSQL or json("col") for MySQL, imported from "drizzle-orm/pg-core" or "drizzle-orm/mysql-core". Chain .$type<YourInterface>() for compile-time TypeScript safety. Drizzle handles serialization and deserialization automatically — no manual JSON.stringify calls needed.

How do I query a JSON field value in Drizzle ORM?

Use the sql tagged template literal. For PostgreSQL text extraction: sql`${table.col}->>'key'`. For containment: sql`${table.col} @> '{"key":"val"}'::jsonb`. For MySQL: sql`JSON_EXTRACT(${table.col}, '$.key')`. These can be used in .where(), .select(), and .orderBy().

What is the difference between json() and jsonb() in Drizzle?

json() maps to PostgreSQL's text-stored json type; jsonb() maps to the binary jsonb type that is 10–30% faster for path queries and supports GIN indexing. For MySQL, only json() exists. Use jsonb() for all new PostgreSQL columns unless you specifically need key-order preservation or byte-for-byte fidelity.

How do I add TypeScript types to a JSON column in Drizzle?

Chain .$type<T>() on the column definition: jsonb("metadata").$type<UserMetadata>(). This adds compile-time type checking on inserts and makes selects return T | null. There is no runtime enforcement — use drizzle-zod for that.

How do I use Drizzle with Zod for JSON validation?

Install drizzle-zod and use createSelectSchema(table) and createInsertSchema(table). Extend the generated schema with .extend({ col: myZodSchema }) to override the JSON column type from z.unknown() to a specific shape. Call schema.safeParse(row) after every database read to catch shape mismatches from schema drift.

How do I index a JSON column in Drizzle ORM?

Add index('idx_name').using('gin').on(table.col) in the table's third argument (the extras callback). This generates a CREATE INDEX ... USING gin(col) migration. GIN indexes accelerate @> containment and ? existence queries on jsonb columns. For single-field path queries, a B-tree expression index on sql`(col->>'key')` is more efficient.

Using TypeORM or Sequelize instead?

The same JSONB column patterns — path queries, GIN indexes, TypeScript generics — apply to all three ORMs. See the equivalent guides for JSON in TypeORM and JSON in Sequelize.

Open JSON Validator