JSON Columns in TypeORM: jsonb, Queries, and Type Safety

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

TypeORM maps JSON database columns to JavaScript objects automatically using the type: 'jsonb' column decorator — no manual JSON.stringify or JSON.parse needed. Define a column with @Column(({ type: 'jsonb' })) and TypeORM handles serialization on write and parsing on read. PostgreSQL's jsonb type stores data in binary form, supporting GIN indexes and 10–30% faster path queries than the text-based json type. For MySQL, use type: 'json'. Without a transformer, TypeORM types JSON columns as object, which provides no compile-time safety. Use a transformer: { to, from } pair to enforce a specific TypeScript interface and validate data at the ORM layer. This guide covers jsonb column definitions, path-operator queries with QueryBuilder, column transformers for TypeScript safety, GIN index creation, and class-transformer integration.

Defining a jsonb Column with @Column

Bottom line: use @Column(({ type: 'jsonb' })) for PostgreSQL and @Column(({ type: 'json' })) for MySQL. TypeORM calls JSON.stringify on write and JSON.parse on read automatically. Without a transformer, the column property type is object.

PostgreSQL's jsonb stores data in binary-decomposed form: keys are sorted, duplicate keys are removed, and insignificant whitespace is stripped on write. This makes path queries 10–30% faster than the text-based json type and enables GIN indexing. Use type: 'json' only when you need byte-for-byte fidelity of the original input. MySQL has a single json column type that uses its own binary storage internally. Add nullable: true to allow null values or supply default: {} to provide a default empty object. The @Column decorator accepts both options alongside type.

import {
  Entity, PrimaryGeneratedColumn, Column, CreateDateColumn
} from "typeorm"

// --- PostgreSQL: jsonb column (binary, GIN-indexable) ---
@Entity("product")
export class Product {
  @PrimaryGeneratedColumn()
  id: number

  @Column({ length: 255 })
  name: string

  // jsonb: no transformer — TypeScript sees this as object
  @Column({ type: "jsonb", nullable: true })
  metadata: object | null

  // jsonb with default empty object
  @Column({ type: "jsonb", default: {} })
  settings: object

  @CreateDateColumn()
  createdAt: Date
}

// --- json vs jsonb: comparison ---
// type: "json"  — stores text verbatim, preserves key order
// type: "jsonb" — stores binary, 10–30% faster path queries, GIN indexes

// --- MySQL: json column (MySQL manages binary storage internally) ---
@Entity("product_mysql")
export class ProductMysql {
  @PrimaryGeneratedColumn()
  id: number

  @Column({ length: 255 })
  name: string

  // MySQL json column — same API as PostgreSQL json
  @Column({ type: "json", nullable: true })
  attributes: object | null
}

// --- Inserting: TypeORM serializes the object automatically ---
// const repo = dataSource.getRepository(Product)
// await repo.save({
//   name: "Widget",
//   metadata: { sku: "WDG-001", tags: ["sale"], price: 9.99 },
// })
//
// --- Reading: TypeORM parses the JSON string back to an object ---
// const product = await repo.findOneBy({ id: 1 })
// product.metadata  →  { sku: "WDG-001", tags: ["sale"], price: 9.99 }

TypeScript Type Safety with Column Transformers

Bottom line: a column transformer with to(value: T): object and from(value: object): T gives the JSON column a specific TypeScript type at the ORM layer. After adding it, inserts that pass the wrong shape fail at compile time.

Without a transformer, TypeORM infers the column property as object. This still works at runtime — serialization and deserialization happen correctly — but TypeScript cannot check the shape of the data you read or write. A transformer bridges the gap: the to() function receives your typed value before the database write; from() receives the raw parsed value after the database read. Both run at the ORM layer with no additional database calls, so there is zero runtime overhead. Declare the entity property type as your interface (e.g., Metadata) and TypeScript enforces the shape on every assignment. For runtime validation (not just compile-time), add class-transformer: annotate the property with @Type(() => MetadataDto) and call plainToInstance() after each read.

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm"
import type { ValueTransformer } from "typeorm"

// --- Define the TypeScript interface for the JSON shape ---
interface Metadata {
  plan: "free" | "pro" | "enterprise"
  features: string[]
  createdAt: string
}

// --- Column transformer: to() runs on write, from() runs on read ---
const metadataTransformer: ValueTransformer = {
  to(value: Metadata): object {
    // Runs before INSERT / UPDATE — value is already an object for jsonb
    return value
  },
  from(value: object): Metadata {
    // Runs after SELECT — cast the parsed JSON to the typed interface
    return value as Metadata
  },
}

@Entity("subscription")
export class Subscription {
  @PrimaryGeneratedColumn()
  id: number

  @Column({ length: 255 })
  userId: string

  // With transformer: property type is Metadata, not object
  @Column({ type: "jsonb", transformer: metadataTransformer })
  metadata: Metadata

  @Column({ type: "jsonb", nullable: true, transformer: metadataTransformer })
  previousMetadata: Metadata | null
}

// --- TypeScript enforces the shape on writes ---
// const repo = dataSource.getRepository(Subscription)
// await repo.save({
//   userId: "user-123",
//   metadata: {
//     plan: "pro",            // must be "free" | "pro" | "enterprise"
//     features: ["analytics"],
//     createdAt: new Date().toISOString(),
//   },
//   // metadata: { plan: "invalid" }  ← TypeScript error at compile time
// })
//
// --- Reads return typed Metadata, not object ---
// const sub = await repo.findOneBy({ id: 1 })
// sub.metadata.plan       →  "pro"    (typed as "free" | "pro" | "enterprise")
// sub.metadata.features   →  string[] (typed)

// --- class-transformer integration for runtime validation ---
// import { Type } from "class-transformer"
// import { IsString, IsArray, IsDateString } from "class-validator"
//
// export class MetadataDto {
//   @IsString() plan!: string
//   @IsArray() features!: string[]
//   @IsDateString() createdAt!: string
// }
//
// @Column({ type: "jsonb", transformer: metadataTransformer })
// @Type(() => MetadataDto)
// metadata!: MetadataDto

Querying JSON Fields with QueryBuilder

Bottom line: TypeORM's QueryBuilder has no built-in JSON path operator helpers — use .where() with raw SQL fragments. Always pass values via the parameter object (second argument) to avoid SQL injection.

PostgreSQL provides two families of JSON operators. The arrow operators (->, ->>, #>, #>>>) extract values by key or path. The containment and existence operators (@>, <@, ?, ?|, ?&) test whether a jsonb value contains another or whether a key exists — these are the operators that benefit from GIN indexes. MySQL uses function syntax: JSON_EXTRACT(col, '$.key') or the shorthand col->'$.key'. Full-text search over jsonb is possible with jsonb_to_tsvector(). The table below lists the key PostgreSQL operators.

import { dataSource } from "./data-source"
import { Subscription } from "./subscription.entity"

const repo = dataSource.getRepository(Subscription)

// --- PostgreSQL: ->> extracts a field as text ---
const proUsers = await repo
  .createQueryBuilder("s")
  .where("s.metadata->>'plan' = :plan", { plan: "pro" })
  .getMany()

// --- PostgreSQL: @> containment (uses GIN index if present) ---
const withAnalytics = await repo
  .createQueryBuilder("s")
  .where("s.metadata @> :filter::jsonb", {
    filter: JSON.stringify({ features: ["analytics"] }),
  })
  .getMany()

// --- Order by a JSON field value ---
const sorted = await repo
  .createQueryBuilder("s")
  .orderBy("s.metadata->>'plan'", "ASC")
  .getMany()

// --- MySQL: JSON_EXTRACT in where clause ---
// const mysqlUsers = await repo
//   .createQueryBuilder("s")
//   .where("JSON_EXTRACT(s.metadata, '$.plan') = :plan", { plan: "pro" })
//   .getMany()

// --- Full-text search via jsonb_to_tsvector ---
const searchResults = await repo
  .createQueryBuilder("s")
  .where(
    "to_tsvector('english', s.metadata::text) @@ plainto_tsquery(:q)",
    { q: "analytics api" }
  )
  .getMany()

// --- JSONPath query (PostgreSQL 12+) ---
// jsonb_path_query_first returns the first match of a JSONPath expression
const expensive = await dataSource.query(
  `SELECT * FROM subscription
   WHERE jsonb_path_exists(metadata, '$.features[*] ? (@ == "sso")')`
)

GIN Indexes for jsonb Performance

Bottom line: create a GIN index via a TypeORM migration using queryRunner.query("CREATE INDEX ... USING gin(col)"). On a 1M-row table, this reduces @> containment queries from an 800ms sequential scan to a 4ms index scan.

GIN (Generalized Inverted Index) decomposes each jsonb value into its key-value pairs and builds an inverted index. The default GIN operator class supports all jsonb operators: @>, ?, ?|, and ?&. The jsonb_path_ops operator class is 3x smaller (higher compression) but only supports the @> containment operator. Use jsonb_path_ops when your queries exclusively use @> and storage size matters. For equality queries on a single JSON path (e.g., metadata->>'plan' = 'pro'), a B-tree expression index is more efficient than GIN — it avoids GIN's higher write overhead. GIN indexes have approximately 3x the write cost of B-tree indexes; they perform best on columns that are read-heavy and infrequently updated.

import { MigrationInterface, QueryRunner } from "typeorm"

// --- Migration: add jsonb column + GIN index ---
export class AddMetadataGinIndex1716800000000 implements MigrationInterface {
  async up(queryRunner: QueryRunner): Promise<void> {
    // Add the jsonb column if it doesn't exist yet
    await queryRunner.query(`
      ALTER TABLE "subscription"
      ADD COLUMN IF NOT EXISTS "metadata" jsonb NOT NULL DEFAULT '{}'
    `)

    // Default GIN: supports @>, ?, ?|, ?& operators
    await queryRunner.query(`
      CREATE INDEX IF NOT EXISTS "idx_subscription_metadata_gin"
      ON "subscription"
      USING gin("metadata")
    `)

    // jsonb_path_ops: 3x smaller, @> only — better when all queries use containment
    // await queryRunner.query(`
    //   CREATE INDEX "idx_subscription_metadata_path_ops"
    //   ON "subscription"
    //   USING gin("metadata" jsonb_path_ops)
    // `)
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP INDEX IF EXISTS "idx_subscription_metadata_gin"`)
    await queryRunner.query(`ALTER TABLE "subscription" DROP COLUMN IF EXISTS "metadata"`)
  }
}

// --- B-tree expression index for equality on a single path ---
// More efficient than GIN for "plan = ?" queries:
export class AddPlanExpressionIndex1716800000001 implements MigrationInterface {
  async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      CREATE INDEX IF NOT EXISTS "idx_subscription_plan"
      ON "subscription" (("metadata"->>'plan'))
    `)
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP INDEX IF EXISTS "idx_subscription_plan"`)
  }
}

// --- Query plan comparison (1M rows) ---
// Without GIN index:
//   EXPLAIN ANALYZE SELECT * FROM subscription WHERE metadata @> '{"plan":"pro"}'
//   → Seq Scan on subscription  (cost=0.00..42000.00)  actual time=0.05..820ms
//
// With GIN index (idx_subscription_metadata_gin):
//   → Bitmap Index Scan on idx_subscription_metadata_gin  actual time=0.2..4ms

JSON Queries with the Repository API

Bottom line: TypeORM's repository.find() does not support JSON path operators in its where option — use repository.createQueryBuilder() for any JSON path filtering. For simple reads and full-document updates, the repository API works fine.

repository.find() and repository.findOne() support equality checks on top-level columns, but JSON path operators like ->> and @> are not available in the TypeORM FindOperator set. The workaround is to switch to repository.createQueryBuilder() any time you need JSON path filtering. For JSON partial updates, the safest approach is a read-modify-write cycle: load the entity, mutate the JavaScript object, call repository.save(). This sends a full-document replace, not a partial update. On high-write tables with 100+ concurrent updates per second, the read-modify-write pattern creates N+1 round-trips. Avoid it by using queryRunner.query() with jsonb_set() for atomic in-place updates.

import { dataSource } from "./data-source"
import { Subscription } from "./subscription.entity"

const repo = dataSource.getRepository(Subscription)

// --- Simple find by primary key: JSON column is auto-deserialized ---
const sub = await repo.findOne({ where: { id: 1 } })
// sub?.metadata.plan  →  "pro"  (typed via transformer)

// --- repository.find() with top-level column equality (works fine) ---
const activeSubs = await repo.find({ where: { userId: "user-123" } })

// --- JSON path filter: must use createQueryBuilder ---
const proSubs = await repo
  .createQueryBuilder("s")
  .where("s.metadata->>'plan' = :plan", { plan: "pro" })
  .getMany()

// --- JSON partial update: read-modify-write ---
// Safe for low-concurrency tables; causes N+1 for bulk operations
async function addFeature(subId: number, feature: string) {
  const sub = await repo.findOne({ where: { id: subId } })
  if (!sub) throw new Error("Subscription not found")

  // Mutate the in-memory object
  sub.metadata = {
    ...sub.metadata,
    features: [...sub.metadata.features, feature],
  }

  await repo.save(sub)   // TypeORM sends a full UPDATE
}

// --- Avoid N+1 for bulk updates: use QueryBuilder UPDATE with jsonb_set ---
// Updates 5,000 rows in a single round-trip instead of 5,000 read-modify-writes
await repo
  .createQueryBuilder()
  .update(Subscription)
  .set({
    metadata: () =>
      `jsonb_set(metadata, '{features}', metadata->'features' || '["sso"]'::jsonb)`,
  })
  .where("metadata->>'plan' = :plan", { plan: "enterprise" })
  .execute()
// Affected rows: however many enterprise subscriptions exist — zero extra reads

Migrations and Schema Evolution for JSON Columns

Bottom line: JSON columns have no schema enforcement at the database level — any valid JSON is accepted. All validation must happen at the application layer (column transformer + class-validator). Migrations that change the JSON shape must use jsonb_set() or a full rewrite to backfill existing rows.

Adding a jsonb column is a single ALTER TABLE ... ADD COLUMN. Adding a NOT NULL constraint with a default requires two steps in PostgreSQL 11 and earlier: add the column as nullable, backfill, then add the constraint. PostgreSQL 12+ can set a constant default atomically without rewriting the table. When evolving the JSON shape — for example, renaming a key or adding a required field — run a migration that calls jsonb_set() to patch existing rows. Backfill migrations on tables with over 1M rows should run in batches of 10,000 rows to avoid long-running transactions that block other queries. After any schema change, verify that the column transformer interface and the application code match the new shape.

import { MigrationInterface, QueryRunner } from "typeorm"

export class JsonColumnEvolution1716900000000 implements MigrationInterface {
  async up(queryRunner: QueryRunner): Promise<void> {
    // 1. Add jsonb column (nullable first for safety)
    await queryRunner.query(`
      ALTER TABLE "product"
      ADD COLUMN IF NOT EXISTS "metadata" jsonb
    `)

    // 2. Backfill existing rows with a default shape
    await queryRunner.query(`
      UPDATE "product"
      SET "metadata" = '{"version":1,"tags":[]}'::jsonb
      WHERE "metadata" IS NULL
    `)

    // 3. Add NOT NULL constraint after backfill
    await queryRunner.query(`
      ALTER TABLE "product"
      ALTER COLUMN "metadata" SET NOT NULL,
      ALTER COLUMN "metadata" SET DEFAULT '{}'::jsonb
    `)

    // 4. Rename a key in existing data using jsonb_set + jsonb - operator
    // Renames "sku" to "productCode" across all rows atomically
    await queryRunner.query(`
      UPDATE "product"
      SET "metadata" = (metadata - 'sku') || jsonb_build_object('productCode', metadata->>'sku')
      WHERE metadata ? 'sku'
    `)

    // 5. Add a required field with a computed default (batch of 10,000)
    // Use a DO block for large tables to avoid long-running transactions
    await queryRunner.query(`
      DO $$
      DECLARE
        batch_size INT := 10000;
        offset_val INT := 0;
        rows_updated INT;
      BEGIN
        LOOP
          UPDATE "product"
          SET metadata = jsonb_set(metadata, '{version}', '2'::jsonb)
          WHERE id IN (
            SELECT id FROM "product"
            WHERE NOT (metadata ? 'version')
            ORDER BY id
            LIMIT batch_size OFFSET offset_val
          );

          GET DIAGNOSTICS rows_updated = ROW_COUNT;
          EXIT WHEN rows_updated = 0;
          offset_val := offset_val + batch_size;
        END LOOP;
      END $$
    `)
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`ALTER TABLE "product" DROP COLUMN IF EXISTS "metadata"`)
  }
}

// --- Important: the DB enforces NO schema on the JSON column ---
// Any valid JSON string is accepted. The following all succeed at the DB level:
//   INSERT INTO product (metadata) VALUES ('{"anything": true}')
//   INSERT INTO product (metadata) VALUES ('[]')
//   INSERT INTO product (metadata) VALUES ('null')
//
// Shape enforcement lives entirely in the column transformer + class-validator.
// Use @IsObject(), @ValidateNested(), and @IsDefined() to reject bad shapes
// before they reach repository.save().

Key Terms

jsonb

A PostgreSQL column type that stores JSON in binary-decomposed form. Keys are sorted, duplicate keys are removed, and insignificant whitespace is stripped. Supports GIN indexes and is 10–30% faster than json for path queries. Use type: 'jsonb' in TypeORM.

JSON column transformer

A TypeORM ValueTransformer with to(value: T): object and from(value: object): T methods. The to() function runs before writes; from() runs after reads. Enforces a TypeScript interface on a JSON column at the ORM layer.

GIN index

Generalized Inverted Index. Decomposes each jsonb value into its key-value pairs and builds an inverted index. Accelerates @> containment, ? existence, ?|, and ?& operators from O(n) full scans to O(log n).

Containment operator (@>)

A PostgreSQL jsonb operator that returns true if the left value contains all key-value pairs of the right value. Example: {"a":1,"b":2} @> {"a":1} is true. Used with QueryBuilder: .where("metadata @> :f::jsonb", { f: '{"plan":"pro"}' }).

JSONPath

A query language for JSON, supported by PostgreSQL 12+ via jsonb_path_query(), jsonb_path_exists(), and jsonb_path_query_first(). Supports filter expressions: $.items[*] ? (@.price > 10).

QueryBuilder

TypeORM's fluent query construction API. Accessed via repository.createQueryBuilder(alias) or dataSource.createQueryBuilder(). Required for JSON path operators, since the find() API does not support them.

FAQ