Prisma Json Field to SQL JSONB and JSON Columns

Json Type Mapping

Prisma's Json type allows storing arbitrary JSON data in a single column. The SQL output depends on the dialect's JSON support.

Default Mappings

Prisma	PostgreSQL	MySQL	SQLite
Json	JSONB	JSON	JSON

Example Prisma Schema

model UserPreference {
  id        Int    @id @default(autoincrement())
  userId    Int    @unique @map("user_id")
  theme     String @default("dark")
  settings  Json
  metadata  Json?

  @@map("user_preferences")
}

Generated SQL (PostgreSQL)

CREATE TABLE user_preferences (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL UNIQUE,
  theme VARCHAR(255) NOT NULL DEFAULT 'dark',
  settings JSONB NOT NULL,
  metadata JSONB
);

JSONB vs JSON in PostgreSQL

PostgreSQL offers two JSON types:

• JSON: Stores the raw JSON text. Preserves whitespace, key ordering, and duplicate keys. Parsing happens on every access.
• JSONB: Stores JSON in a decomposed binary format. Faster reads, supports indexing (GIN indexes), but slightly slower writes. The converter defaults to JSONB for PostgreSQL as it's the recommended choice for most use cases.

MySQL JSON

MySQL's JSON type validates and stores JSON in a binary format (similar to JSONB). It supports indexing via generated columns and has built-in JSON functions for querying.

SQLite JSON

SQLite stores JSON as regular text (since version 3.38.0 there's a JSON subtype, but storage is still text). SQLite provides JSON functions for querying, but there's no native validation on insert.

When to Use Json Fields

Json fields are ideal for semi-structured data like user preferences, configuration objects, or API response caches where the schema may vary between records. For data with a fixed structure, consider using separate columns instead for better query performance and type safety.