JSON Columns in Objection.js: jsonSchema, 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

Objection.js is a SQL ORM built on top of Knex.js that adds model classes, jsonSchema validation, relation mapping, and eager loading. For JSON columns, Objection uses Knex's underlying table.jsonb() migration API — define the column in a Knex migration. On the model side, declare the property with jsonSchema for validation: { type: 'object', properties: { plan: { type: 'string' }, features: { type: 'array', items: { type: 'string' } } } }. Objection validates JSON field values against jsonSchema on every insert and update — invalid values throw a ValidationError with detailed field-level messages. For path queries, Objection passes the where clause to Knex, so use Knex's knex.raw() for JSON operators: Product.query().whereRaw("metadata->>'plan' = ?", ['pro']). Objection's $related and eager loading relations work with JSON columns too. This guide covers JSON column migrations with Knex, jsonSchema property validation, raw path queries, TypeScript model typing, eager loading with JSON columns, and partial JSON updates with patch().

JSON Column Migrations with Knex

Bottom line: Objection.js has no migration API — use Knex's knex.schema.createTable() and knex.schema.alterTable() directly. For PostgreSQL, prefer table.jsonb() (binary, indexable). For MySQL, use table.json().

Knex migrations live in the directory specified by the migrations.directory key in knexfile.js. Each migration file exports an up and a down function. The up function creates or alters the schema; the down function reverses it. Run migrations with knex migrate:latest and roll back with knex migrate:rollback. After the migration runs, the metadata column is available in every Objection query with no model-level change required — Objection reads whatever columns Knex returns. To add a jsonb column to an existing table, use table.jsonb('metadata').nullable() inside alterTable — the column value for existing rows will be NULL until set. There are 3 distinct column flavors: jsonb (PostgreSQL binary), json (PostgreSQL text or MySQL), and text with manual serialization. Always use jsonb on PostgreSQL — it supports GIN indexes and is 10–30% faster for path queries.

// migrations/20260527_create_products.js

exports.up = async function (knex) {
  // --- PostgreSQL: jsonb column with a default empty object ---
  await knex.schema.createTable('products', (table) => {
    table.increments('id').primary()
    table.string('name').notNullable()
    // jsonb: binary storage, supports GIN index, faster for path queries
    table.jsonb('metadata').defaultTo('{}').notNullable()
    table.timestamps(true, true)
  })
}

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

// --- MySQL: use table.json() instead of table.jsonb() ---
// exports.up = async function (knex) {
//   await knex.schema.createTable('products', (table) => {
//     table.increments('id').primary()
//     table.string('name').notNullable()
//     table.json('metadata')            // MySQL has a single JSON type
//   })
// }

// --- Adding a jsonb column to an existing table ---
// exports.up = async function (knex) {
//   await knex.schema.alterTable('products', (table) => {
//     table.jsonb('metadata').nullable()   // NULL for existing rows until set
//   })
// }
// exports.down = async function (knex) {
//   await knex.schema.alterTable('products', (table) => {
//     table.dropColumn('metadata')
//   })
// }

// Run:  knex migrate:latest
// Roll: knex migrate:rollback

jsonSchema Validation for JSON Fields

Bottom line: Objection's static jsonSchema property validates the entire model instance on every insert() and patch(). Invalid JSON field values throw a ValidationError with field-level error details at err.data.

Objection uses Ajv (Another JSON Validator) internally — it supports JSON Schema draft-07 keywords including enum, additionalProperties, anyOf, oneOf, and$ref. The jsonSchema covers the whole row, not just the JSON column: list all columns you want validated under properties. For the JSON column itself, nest a schema inside the property definition. Objection validates on $query().insert() and $query().patch() — it does not validate on raw knex queries or bulk inserts that bypass the model layer. A missing required field throws a ValidationError where err.data.fieldName contains an array of 1 error object with message, keyword, and params properties. Set additionalProperties: false inside the nested JSON schema to reject unexpected keys — useful for preventing schema drift from client payloads.

import { Model, ValidationError } from 'objection'
import Knex from 'knex'

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

// --- Full model with jsonSchema for the metadata jsonb column ---
class Product extends Model {
  static tableName = 'products'

  // Objection validates every insert and patch against this schema (Ajv / JSON Schema draft-07)
  static jsonSchema = {
    type: 'object',
    required: ['name'],
    properties: {
      name: { type: 'string', minLength: 1, maxLength: 255 },
      metadata: {
        type: 'object',
        properties: {
          plan: {
            type: 'string',
            enum: ['free', 'pro', 'enterprise'],   // ValidationError if value not in list
          },
          features: {
            type: 'array',
            items: { type: 'string' },
            maxItems: 20,
          },
          score: { type: 'number', minimum: 0, maximum: 100 },
        },
        additionalProperties: false,   // reject unknown JSON keys
      },
    },
  }
}

// --- Valid insert: passes jsonSchema ---
await Product.query().insert({
  name: 'Widget Pro',
  metadata: { plan: 'pro', features: ['analytics', 'api'], score: 87 },
})

// --- Invalid insert: ValidationError thrown ---
try {
  await Product.query().insert({
    name: 'Bad Widget',
    metadata: { plan: 'premium' },   // 'premium' is not in the enum
  })
} catch (err) {
  if (err instanceof ValidationError) {
    // err.data: { 'metadata.plan': [{ message: 'must be equal to one of the allowed values', keyword: 'enum' }] }
    console.error(err.data)
  }
}

// --- anyOf: accept multiple valid JSON shapes ---
class Event extends Model {
  static tableName = 'events'
  static jsonSchema = {
    type: 'object',
    properties: {
      payload: {
        anyOf: [
          { type: 'object', properties: { type: { const: 'click' }, x: { type: 'number' }, y: { type: 'number' } }, required: ['type', 'x', 'y'] },
          { type: 'object', properties: { type: { const: 'keypress' }, key: { type: 'string' } }, required: ['type', 'key'] },
        ],
      },
    },
  }
}

JSON Path Queries with whereRaw()

Bottom line: Objection's query builder has no JSON-specific operator helpers — pass raw SQL to whereRaw() for path filtering. Use parameterized placeholders (?) for all user-supplied values.

The -> operator extracts a JSON value as a json type; ->> extracts it as text (use for string comparisons in WHERE clauses). Chain operators for nested paths: metadata->'nested'->>'key'. The @> containment operator on jsonb columns checks whether the left value contains all key-value pairs from the right — and it uses GIN indexes if one exists. For MySQL, use JSON_EXTRACT(col, '$.key'). Objection passes allwhereRaw calls directly to Knex, which forwards them to the database driver with no transformation. Call .toKnexQuery() on an Objection query to get the underlying Knex builder when you need Knex-specific methods not exposed by Objection's API. Always use parameterized queries — never interpolate user values directly into the SQL string.

import { Model } from 'objection'
import Knex from 'knex'

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

class Product extends Model {
  static tableName = 'products'
}

// --- PostgreSQL: ->> extracts a JSON field as text ---
const proProducts = await Product.query()
  .whereRaw("metadata->>'plan' = ?", ['pro'])

// --- PostgreSQL: nested path ---
const highScore = await Product.query()
  .whereRaw("(metadata->>'score')::numeric > ?", [80])

// --- PostgreSQL: @> containment (uses GIN index if defined) ---
// Finds rows where metadata contains { active: true }
const activeProducts = await Product.query()
  .whereRaw('metadata @> ?::jsonb', [JSON.stringify({ active: true })])

// --- MySQL: JSON_EXTRACT ---
// const proProducts = await Product.query()
//   .whereRaw("JSON_EXTRACT(metadata, '$.plan') = ?", ['pro'])

// --- Order by a JSON field ---
const ranked = await Product.query()
  .whereRaw("metadata->>'score' IS NOT NULL")
  .orderByRaw("(metadata->>'score')::numeric DESC")
  .limit(10)

// --- Select a JSON field as a named column ---
const plans = await Product.query()
  .select('id', knex.raw("metadata->>'plan' AS plan"))
// plans[0].plan → 'pro'  (string)

// --- Multiple conditions ---
const filtered = await Product.query()
  .whereRaw("metadata->>'plan' != ?", ['free'])
  .where('id', '>', 100)
  .orderBy('id')

// --- Drop to Knex builder for complex queries ---
const knexQuery = Product.query()
  .whereRaw("metadata @> ?::jsonb", [JSON.stringify({ plan: 'pro' })])
  .toKnexQuery()                       // returns a Knex QueryBuilder

knexQuery.limit(5).offset(10)         // Knex-specific chaining
const rows = await knexQuery

TypeScript Model Typing for JSON Fields

Bottom line: declare JSON columns as typed properties on the model class. Use ModelObject<Product> for plain object types and PartialModelObject<Product> for partial update payloads. The TypeScript types and jsonSchema do not derive from each other — keep them in sync manually.

Objection v3 ships with improved TypeScript declarations. Model properties declared with ! (definite assignment assertion) tell TypeScript the column will be populated after a query. Query builder methods are fully typed: Product.query() returns QueryBuilder<Product, Product[]>, .findById(1) returns QueryBuilder<Product, Product | undefined>. The ModelObject<T> utility type strips all Objection model methods and returns a plain object type — useful for function parameters and API response types. PartialModelObject<T> makes all properties optional and accepts Raw expressions, so it matches what .patch() accepts. Override $formatJson() to control JSON serialization when calling .toJSON() or when Objection auto-serializes a result set — use it to omit internal fields or rename keys before sending to clients. Use $beforeInsert and $beforeUpdate hooks for pre-insert logic that goes beyond what JSON Schema can express, such as cross-field checks or async validations.

import { Model, ModelObject, PartialModelObject } from 'objection'
import Knex from 'knex'

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

// --- TypeScript interface for the JSON column ---
interface ProductMetadata {
  plan: 'free' | 'pro' | 'enterprise'
  features: string[]
  score: number
}

// --- Typed Objection model ---
class Product extends Model {
  static tableName = 'products'

  // Column properties with definite assignment assertion
  id!: number
  name!: string
  metadata!: ProductMetadata    // typed JSON column
  createdAt!: string

  static jsonSchema = {
    type: 'object',
    required: ['name'],
    properties: {
      name: { type: 'string' },
      metadata: {
        type: 'object',
        properties: {
          plan: { type: 'string', enum: ['free', 'pro', 'enterprise'] },
          features: { type: 'array', items: { type: 'string' } },
          score: { type: 'number' },
        },
      },
    },
  }

  // Control JSON serialization — omit 'score' from API responses
  $formatJson(json: ModelObject<Product>) {
    const formatted = super.$formatJson(json)
    // Remove internal field from API output
    delete (formatted.metadata as Partial<ProductMetadata>).score
    return formatted
  }

  // Lifecycle hook: called before every insert
  async $beforeInsert() {
    // Async cross-field validation beyond what jsonSchema supports
    if (this.metadata?.plan === 'enterprise' && !this.metadata?.features?.length) {
      throw new Error('Enterprise plan requires at least 1 feature')
    }
  }
}

// --- ModelObject<T>: plain object without Objection methods ---
type ProductRow = ModelObject<Product>
// ProductRow.metadata → ProductMetadata  (no Model methods)

// --- PartialModelObject<T>: for patch() payloads ---
type ProductPatch = PartialModelObject<Product>
// All fields optional; also accepts Raw expressions for jsonb_set

// --- Typed query — returns Product[] ---
const products: Product[] = await Product.query()
  .whereRaw("metadata->>'plan' = ?", ['pro'])

// --- findById returns Product | undefined ---
const product: Product | undefined = await Product.query().findById(1)

// --- TypeScript-safe insert ---
await Product.query().insert({
  name: 'Widget',
  metadata: { plan: 'pro', features: ['analytics'], score: 92 },
  // TypeScript flags if plan value is not 'free' | 'pro' | 'enterprise'
})

Eager Loading and JSON Relations

Bottom line: Objection's withGraphFetched loads relations alongside models that have JSON columns. Objection parses JSON column strings to JavaScript objects automatically — you receive a plain object, not a raw JSON string.

Objection supports 4 relation types: BelongsToOneRelation, HasManyRelation, ManyToManyRelation, and HasOneThroughRelation. Define them in the static relationMappings property on the model. withGraphFetched uses 1 additional query per relation level by default; withGraphJoined uses a single JOIN query and is faster for small result sets but produces larger result rows. The modifiers option on withGraphFetched applies reusable query constraints to the eager-loaded relation — filtering, sorting, or limiting the loaded records. Objection's toJSON() method recursively converts the model instance and all eagerly loaded relations to a plain object, with JSON column values parsed — not as raw strings. Override $formatJson() on any model in the graph to control which fields are serialized in that model's JSON output.

import { Model, ModelObject } from 'objection'
import Knex from 'knex'

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

interface ReviewMetadata { helpful: number; verified: boolean }

class User extends Model {
  static tableName = 'users'
  id!: number
  name!: string
}

class Review extends Model {
  static tableName = 'reviews'
  id!: number
  rating!: number
  body!: string
  reviewMeta!: ReviewMetadata    // JSON column on the review
}

class Product extends Model {
  static tableName = 'products'
  id!: number
  name!: string
  metadata!: { plan: string; features: string[]; score: number }

  // Eagerly loaded relations (populated by withGraphFetched)
  owner?: User
  reviews?: Review[]

  static relationMappings = () => ({
    owner: {
      relation: Model.BelongsToOneRelation,
      modelClass: User,
      join: { from: 'products.user_id', to: 'users.id' },
    },
    reviews: {
      relation: Model.HasManyRelation,
      modelClass: Review,
      join: { from: 'products.id', to: 'reviews.product_id' },
    },
  })
}

// --- Load a single relation ---
const products = await Product.query().withGraphFetched('owner')
// products[0].owner → User instance
// products[0].metadata → { plan: 'pro', features: [...], score: 87 }  (parsed, not a string)

// --- Load multiple relations ---
const withAll = await Product.query().withGraphFetched('[owner, reviews]')

// --- Filter the eager-loaded relation with modifiers ---
const withTopReviews = await Product.query()
  .withGraphFetched('reviews(topRated)')
  .modifiers({
    topRated: (builder) => builder.where('rating', '>=', 4).orderBy('rating', 'desc').limit(5),
  })

// --- JSON column on the eager-loaded relation ---
// withTopReviews[0].reviews[0].reviewMeta → { helpful: 42, verified: true }  (parsed)

// --- toJSON: recursively serializes model + relations, JSON columns parsed ---
const plain = withTopReviews[0].toJSON()
// plain.metadata.plan → 'pro'
// plain.reviews[0].reviewMeta.helpful → 42

// --- $formatJson: omit internal JSON fields from serialized output ---
class ProductPublic extends Product {
  $formatJson(json: ModelObject<ProductPublic>) {
    const out = super.$formatJson(json)
    // Don't expose score in API output
    if (out.metadata && typeof out.metadata === 'object') {
      delete (out.metadata as { score?: number }).score
    }
    return out
  }
}

Partial JSON Updates with patch() and raw()

Bottom line: Objection's .patch() replaces the entire JSON column value when given a plain object. For atomic subfield updates, pass knex.raw() as the column value inside.patch() — this sends a single SQL statement with no round-trip read.

PostgreSQL's jsonb_set(target, path, new_value, create_missing) function updates a single path inside a jsonb value atomically. The path argument is a text array literal: '{plan}' for a top-level key, '{address,city}' for a nested key. The new_value must be a ::jsonb-cast string — use JSON.stringify() to produce the value. The || operator merges two jsonb objects, replacing matching keys and adding new ones. The - operator removes a key from a jsonb object. All 3 operations execute in a single SQL statement without reading the row first, avoiding race conditions under concurrent writes. The alternative fetch-modify-save pattern requires 2 round-trips and is susceptible to lost updates when 2 processes read the same row simultaneously — each overwrites the other's changes. Use knex.raw() in .patch() for any production use case with concurrent writes.

import { Model } from 'objection'
import Knex from 'knex'

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

class Product extends Model {
  static tableName = 'products'
}

// --- patch() with a plain object: replaces the ENTIRE metadata column ---
await Product.query()
  .patch({ metadata: { plan: 'pro', features: ['analytics'], score: 90 } })
  .where('id', 1)
// ✗ Unsafe under concurrent writes — overwrites all other concurrent changes

// --- jsonb_set: atomic subfield update (no read required) ---
await Product.query()
  .patch({
    metadata: knex.raw(
      "jsonb_set(metadata, '{plan}', ?::jsonb, true)",
      [JSON.stringify('pro')]
    ),
  })
  .where('id', 1)
// SQL: UPDATE products SET metadata = jsonb_set(metadata, '{plan}', '"pro"'::jsonb, true) WHERE id = 1

// --- Nested key update: update metadata->'address'->'city' ---
await Product.query()
  .patch({
    metadata: knex.raw(
      "jsonb_set(metadata, '{address,city}', ?::jsonb, true)",
      [JSON.stringify('New York')]
    ),
  })
  .where('id', 1)

// --- || operator: merge/add keys without removing existing ones ---
await Product.query()
  .patch({
    metadata: knex.raw('metadata || ?::jsonb', [JSON.stringify({ newKey: 'value', score: 95 })]),
  })
  .where('id', 1)
// Existing keys not in the patch object are preserved

// --- - operator: delete a key from the jsonb object ---
await Product.query()
  .patch({
    metadata: knex.raw("metadata - 'oldKey'"),
  })
  .where('id', 1)

// --- Append to a JSON array field ---
await Product.query()
  .patch({
    metadata: knex.raw(
      "jsonb_set(metadata, '{features}', (metadata->'features') || ?::jsonb, true)",
      [JSON.stringify(['sso'])]
    ),
  })
  .where('id', 1)

// --- Fetch-modify-save: simpler but has race condition risk ---
async function unsafeUpgrade(id: number) {
  const product = await Product.query().findById(id)    // round-trip 1
  if (!product) throw new Error('Not found')
  const current = product.metadata as { plan: string; features: string[] }
  await Product.query()                                  // round-trip 2 — concurrent write may overwrite
    .patch({ metadata: { ...current, plan: 'pro' } })
    .where('id', id)
}

FAQ