Convert SQL JSON and JSONB Columns to Prisma Json Fields

JSON Columns in Prisma

Both PostgreSQL (JSON, JSONB) and MySQL (JSON) support storing structured JSON data directly in columns. Prisma maps these to the Json scalar type, giving you flexible schema-less storage alongside your typed relational data.

Example SQL

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  metadata JSONB NOT NULL DEFAULT '{}',
  settings JSON,
  tags JSONB NOT NULL DEFAULT '[]'
);

CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

Generated Prisma Schema

model Product {
  id       Int    @id @default(autoincrement())
  name     String
  metadata Json   @default("{}")
  settings Json?
  tags     Json   @default("[]")

  @@map("products")
}

JSON vs JSONB

Feature	JSON	JSONB
Storage	Text (preserves formatting)	Binary (decomposed)
Indexing	Not indexable	GIN / GiST indexing
Operators	Basic	Rich query operators
Performance	Faster writes	Faster reads and queries

Prisma's Json type does not distinguish between JSON and JSONB at the schema level. The converter uses @db.Json or @db.JsonB native annotations when targeting PostgreSQL to preserve the distinction.

Typing JSON in TypeScript

Prisma's Json type maps to Prisma.JsonValue in the generated client, which is string | number | boolean | null | JsonObject | JsonArray. For type safety, define a TypeScript interface and cast:

interface ProductMetadata {
  color?: string;
  dimensions?: { width: number; height: number };
  features?: string[];
}

const product = await prisma.product.findFirst();
const meta = product.metadata as ProductMetadata;

Default Values

JSON defaults in Prisma use string representation:

SQL default	Prisma default
DEFAULT '{}'	@default("{}")
DEFAULT '[]'	@default("[]")
Complex default	@default(dbgenerated("'{\"key\": \"value\"}'"))

When to Use JSON Fields

JSON columns are ideal for:

• Variable attributes — product metadata that varies by category
• Configuration objects — user preferences, feature flags
• Denormalized snapshots — API response caches

Avoid JSON for data that needs to be queried, filtered, or joined frequently. In those cases, normalize into proper relational columns.

GIN Indexing

PostgreSQL's GIN indexes on JSONB columns enable fast containment queries (@>). The converter preserves these indexes, though Prisma currently requires raw queries to use JSONB operators.