JSON Columns in Knex.js: jsonb, Queries, and TypeScript

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

Knex.js handles JSON columns through its migration API and raw query builder. Define a jsonb column in a migration with table.jsonb('metadata') for PostgreSQL or table.json('metadata') for MySQL and MariaDB. Knex does not have a built-in JSON path operator — use knex.raw() for all path expressions. For PostgreSQL, knex('users').where(knex.raw("metadata->>>'plan' = ?", ['pro'])) filters on a nested JSON field. For containment queries, knex.raw("metadata @> ?::jsonb", [JSON.stringify({ active: true })]). Knex serializes JavaScript objects to JSON strings automatically on insert via JSON.stringify — you never call it manually. On reads, the underlying pg or mysql2 driver parses the JSON column back to a JavaScript object. TypeScript integration requires either manual type annotations on query results or knex-types / kanel for generated types. This guide covers migration syntax, jsonb vs json, path queries with knex.raw(), GIN index creation, partial updates with jsonb_set, and TypeScript result typing.

Defining JSON Columns in Migrations

Bottom line: use table.jsonb('metadata') for PostgreSQL (binary storage, GIN-indexable) and table.json('metadata') for MySQL. Both are available in a migration's exports.up function inside the Knex schema builder.

PostgreSQL's jsonb type stores data in a binary-decomposed form. Compared to json, it is 10–30% faster for path operator queries, eliminates duplicate keys, strips insignificant whitespace, and is the only type that supports GIN indexes. For MySQL, only json is available — MySQL stores JSON in its own binary format internally regardless of the type name. Both table.jsonb() and table.specificType('metadata', 'jsonb') produce the same DDL; table.jsonb() is the idiomatic form. Chain .nullable() to allow NULL or .defaultTo(knex.raw("''::jsonb")) to initialize rows with an empty object. To add a jsonb column to a table that already exists, wrap the schema call in knex.schema.alterTable().

// migrations/20260527_create_users.js

// PostgreSQL migration with a jsonb column
exports.up = async function (knex) {
  await knex.schema.createTable('users', (table) => {
    table.increments('id').primary()
    table.string('email').notNullable().unique()

    // jsonb — recommended for PostgreSQL (binary, GIN-indexable)
    table.jsonb('metadata').nullable()

    // jsonb with an empty-object default
    table.jsonb('settings').notNullable().defaultTo(knex.raw("'{}'::jsonb"))

    table.timestamps(true, true)
  })
}

exports.down = async function (knex) {
  await knex.schema.dropTableIfExists('users')
}

// ─────────────────────────────────────────────────────────────────────────────

// MySQL migration — only json is available
exports.up = async function (knex) {
  await knex.schema.createTable('products', (table) => {
    table.increments('id').primary()
    table.string('name').notNullable()

    // MySQL: json() — stored in MySQL's internal binary format
    table.json('attributes').nullable()
  })
}

exports.down = async function (knex) {
  await knex.schema.dropTableIfExists('products')
}

// ─────────────────────────────────────────────────────────────────────────────

// Add a jsonb column to an existing table
exports.up = async function (knex) {
  await knex.schema.alterTable('users', (table) => {
    table.jsonb('preferences').defaultTo(null)
  })
}

exports.down = async function (knex) {
  await knex.schema.alterTable('users', (table) => {
    table.dropColumn('preferences')
  })
}

// ─────────────────────────────────────────────────────────────────────────────

// table.specificType() produces identical DDL — less readable, avoid it
// table.specificType('metadata', 'jsonb')   ← same as table.jsonb('metadata')

Inserting and Reading JSON Data

Bottom line: pass a plain JavaScript object to Knex on insert — it serializes automatically. On select, the database driver returns a parsed JavaScript object with no extra step needed.

When you call knex('users').insert({ metadata: obj } ), Knex calls JSON.stringify(obj) internally before sending the SQL to the database. On reads, knex('users').select('*') returns rows where metadata is already a parsed JavaScript object — the pg driver handles JSON.parse automatically for json and jsonb columns. The mysql2 driver does the same for MySQL json columns. The critical gotcha: manually calling JSON.stringify before inserting causes Knex to double-stringify the value. The database then stores a JSON string encoded as a JSON string — reads return a string instead of an object. Always pass plain objects.

import knex from 'knex'

const db = knex({
  client: 'pg',
  connection: process.env.DATABASE_URL,
})

// --- Insert: pass a plain object — Knex calls JSON.stringify internally ---
await db('users').insert({
  email: 'alice@example.com',
  metadata: {
    plan: 'pro',
    features: ['analytics', 'api-access'],
    createdAt: new Date().toISOString(),
  },
})

// --- Select all: metadata is a parsed JavaScript object ---
const users = await db('users').select('*')
// users[0].metadata  →  { plan: 'pro', features: [...], createdAt: '...' }
// users[0].metadata.plan  →  'pro'

// --- Select a specific JSON column ---
const rows = await db('users').select('metadata').where('id', 1)
// rows[0].metadata  →  { plan: 'pro', features: [...] }

// --- Insert with an array value ---
await db('orders').insert({
  user_id: 1,
  line_items: [
    { productId: 42, quantity: 2, price: 9.99 },
    { productId: 55, quantity: 1, price: 24.50 },
  ],
})

// --- Read back and use the parsed array ---
const [order] = await db('orders').select('line_items').where('id', 1)
const total = order.line_items.reduce(
  (sum: number, item: { price: number; quantity: number }) =>
    sum + item.price * item.quantity,
  0
)
console.log('Order total:', total.toFixed(2))

// --- WRONG: double-stringify — do NOT do this ---
// await db('users').insert({
//   metadata: JSON.stringify({ plan: 'pro' }),  // stored as '"{\"plan\":\"pro\"}"'
// })

Querying JSON Fields with knex.raw()

Bottom line: Knex provides no built-in JSON path operator abstraction. All JSON path queries — filtering, ordering, containment — require knex.raw().

PostgreSQL exposes several JSON operators. -> extracts a value as a JSON type (returns JSON). ->> extracts it as text (suitable for string comparisons in .where()). #> accesses a nested path specified as an array (returns JSON). #>>> does the same returning text. @> checks containment — the left jsonb value contains all key-value pairs of the right. ? checks whether a key exists at the top level. For MySQL, use JSON_EXTRACT(col, '$.key') or the shorthand col->'$.key'. PostgreSQL 12+ also supports the jsonb_path_query_first() function for JSONPath expressions. Always use parameterized queries (? placeholders) to avoid SQL injection.

// PostgreSQL: ->> extracts a JSON field as text
const proUsers = await db('users')
  .where(db.raw("metadata->>'plan' = ?", ['pro']))

// PostgreSQL: @> containment — rows where metadata contains { active: true }
const activeUsers = await db('users')
  .where(db.raw('metadata @> ?::jsonb', [JSON.stringify({ active: true })]))

// PostgreSQL: nested path with #>> (array of path segments, returns text)
const portlandUsers = await db('users')
  .where(db.raw("metadata#>>'{address,city}' = ?", ['Portland']))

// PostgreSQL: order by a JSON field
const sorted = await db('users')
  .orderBy(db.raw("metadata->>'name'"))

// PostgreSQL: key existence with ?
// (use ?? in Knex to escape a literal ? that is not a binding)
const withTags = await db('users')
  .whereRaw("metadata ?? 'tags'")

// PostgreSQL 12+: JSONPath expression
const expensiveItems = await db('orders')
  .select(
    db.raw(
      "jsonb_path_query_first(line_items, '$.items[*] ? (@.price > 100)')::text as item"
    )
  )

// MySQL: JSON_EXTRACT
const mysqlProUsers = await db('users')
  .where(db.raw("JSON_EXTRACT(metadata, '$.plan') = ?", ['pro']))

// MySQL: shorthand arrow syntax
const mysqlActiveUsers = await db('users')
  .where(db.raw("metadata->>'$.active' = ?", ['true']))

GIN Indexes via Migrations

Bottom line: GIN indexes on jsonb columns make containment and key-existence queries O(log n) instead of O(n). Add them via knex.raw() inside a migration's up function.

A GIN (Generalized Inverted Index) decomposes each jsonb value into its key-value pairs and builds an inverted index mapping each element to the rows containing it. On a table with 1 million rows, a containment query using @> drops from approximately 600 ms (full sequential scan) to approximately 2 ms with a GIN index. Two GIN variants exist: the default supports all operators — @>, <@, ?, ?|, ?& — and is the right choice when you need key-existence checks. The jsonb_path_ops variant produces an index roughly 3 times smaller and faster for @> containment only — choose it when you only query by containment and want the smallest index footprint. GIN indexes have higher write amplification than B-tree indexes, so prefer them on columns that are read-heavy relative to write frequency.

// migrations/20260527_add_gin_index.js

// Default GIN index — supports @>, <@, ?, ?|, ?&
exports.up = async function (knex) {
  await knex.raw(
    'CREATE INDEX users_metadata_gin ON users USING GIN (metadata)'
  )
}

exports.down = async function (knex) {
  await knex.raw('DROP INDEX IF EXISTS users_metadata_gin')
}

// ─────────────────────────────────────────────────────────────────────────────

// jsonb_path_ops variant — ~3x smaller, @> containment only
exports.up = async function (knex) {
  await knex.raw(
    'CREATE INDEX users_metadata_gin ON users USING GIN (metadata jsonb_path_ops)'
  )
}

exports.down = async function (knex) {
  await knex.raw('DROP INDEX IF EXISTS users_metadata_gin')
}

// ─────────────────────────────────────────────────────────────────────────────

// B-tree expression index on a single JSON path
// — faster for equality queries on one field, no GIN overhead
exports.up = async function (knex) {
  await knex.raw(
    "CREATE INDEX users_plan_idx ON users ((metadata->>'plan'))"
  )
}

exports.down = async function (knex) {
  await knex.raw('DROP INDEX IF EXISTS users_plan_idx')
}

// ─────────────────────────────────────────────────────────────────────────────

// Queries that hit the GIN index (default variant)

// @> containment — uses GIN
const proUsers = await db('users')
  .where(db.raw("metadata @> ?::jsonb", [JSON.stringify({ plan: 'pro' })]))

// ? key existence — uses GIN
const withSettings = await db('users')
  .whereRaw("metadata ?? 'settings'")

Partial JSON Updates with jsonb_set

Bottom line: Knex has no built-in partial JSON update helper. Use jsonb_set() with knex.raw() in an UPDATE for an atomic server-side patch — no round-trip, no race condition.

The alternative to jsonb_set is the fetch-mutate-save pattern: read the row, merge in JavaScript, write it back. This requires 2 database round-trips and is not safe under concurrent writes — two simultaneous updates can each read the same row and overwrite each other's changes. A single UPDATE with jsonb_set is atomic: PostgreSQL locks the row for the duration of the statement, so no concurrent process can read a stale value. The || merge operator appends or overwrites top-level keys in one statement. The - operator deletes a key. All three forms work inside Knex's .update() by passing a knex.raw() expression as the column value. For nested paths, provide a path array like '{address,city}' as the second argument to jsonb_set.

// Partial update: change a single top-level key atomically
await db('users')
  .where('id', 1)
  .update({
    metadata: db.raw(
      "jsonb_set(metadata, '{plan}', ?::jsonb)",
      [JSON.stringify('pro')]
    ),
  })

// Update a nested field: metadata.address.city
await db('users')
  .where('id', 1)
  .update({
    metadata: db.raw(
      "jsonb_set(metadata, '{address,city}', ?::jsonb)",
      [JSON.stringify('Portland')]
    ),
  })

// Append or overwrite top-level keys with the || merge operator
await db('users')
  .where('id', 1)
  .update({
    metadata: db.raw('metadata || ?::jsonb', [
      JSON.stringify({ active: true, updatedAt: new Date().toISOString() }),
    ]),
  })

// Append a value to a jsonb array field
await db('users')
  .where('id', 1)
  .update({
    metadata: db.raw(
      "jsonb_set(metadata, '{features}', (metadata->'features') || ?::jsonb)",
      [JSON.stringify(['sso'])]
    ),
  })

// Delete a key with the - operator
await db('users')
  .where('id', 1)
  .update({
    metadata: db.raw("metadata - 'legacyField'"),
  })

// ─────────────────────────────────────────────────────────────────────────────
// Fetch-mutate-save (safe only for single-writer scenarios)
const [user] = await db('users').select('metadata').where('id', 1)
const updated = { ...user.metadata, plan: 'enterprise' }
await db('users').where('id', 1).update({ metadata: updated })
// Risk: concurrent UPDATE between the SELECT and UPDATE overwrites changes

TypeScript Typing for JSON Query Results

Bottom line: Knex supports TypeScript generics — pass your interface as knex<User>('users') to type query results. For raw queries, cast manually. Use kanel to generate interfaces from the live database schema.

Knex's TypeScript generics work at the query level: knex<User>('users').select('*') returns Promise<User[]>. Define a top-level interface and a nested interface for the JSON field — Knex trusts your declaration and does not validate it at runtime. For .first(), the return type is Promise<User | undefined>. The Knex builder loses the generic type when you use .where(knex.raw(...)) — the raw expression breaks TypeScript's type narrowing, and you may need to cast the result with as User[]. For raw queries, provide the generic directly: knex.raw<{ rows: UserMetadata[] }}("..."). Tools like kanel (an open-source schema introspection tool) connect to your PostgreSQL database and generate TypeScript interfaces for every table and column, including jsonb columns typed as unknown by default with override support. knex-types provides similar functionality. Both tools integrate into a build step so your types stay in sync with schema migrations.

import knex from 'knex'

const db = knex({ client: 'pg', connection: process.env.DATABASE_URL })

// --- Define TypeScript interfaces for the JSON column ---
interface UserMetadata {
  plan: 'free' | 'pro' | 'enterprise'
  features: string[]
  createdAt: string
  address?: { city: string; country: string }
}

interface User {
  id: number
  email: string
  metadata: UserMetadata | null
}

// --- Typed select: knex<User>('users') returns Promise<User[]> ---
const users: User[] = await db<User>('users').select('*')
// users[0].metadata?.plan  →  'pro'  (typed, no cast needed)

// --- Typed first(): returns Promise<User | undefined> ---
const user = await db<User>('users').where('id', 1).first()
if (user) {
  console.log(user.metadata?.features)  // string[] | undefined
}

// --- Typed insert: TypeScript checks the shape against User ---
await db<User>('users').insert({
  email: 'bob@example.com',
  metadata: { plan: 'free', features: [], createdAt: new Date().toISOString() },
})

// --- Raw query: provide generic for the pg result shape ---
const result = await db.raw<{ rows: { metadata: UserMetadata }[] }>(
  'SELECT metadata FROM users WHERE id = ?',
  [1]
)
const metadata: UserMetadata = result.rows[0].metadata

// --- Cast when .where(knex.raw()) breaks type inference ---
const proUsers = await db('users')
  .where(db.raw("metadata->>'plan' = ?", ['pro'])) as User[]

// --- kanel: generate interfaces from your live DB schema ---
// npm install --save-dev kanel
// kanel.config.js:
// module.exports = {
//   connection: process.env.DATABASE_URL,
//   outputPath: './src/db/types',
// }
// npx kanel  →  generates User.ts, UserMetadata.ts, etc.

FAQ