JSON Fields in Prisma ORM

Written and reviewed by the Jsonic editorial team — every guide is verified against the official spec or runtime before publication.

Last updated: May 19, 2026

Prisma's Json field type lets you store arbitrary JSON alongside structured relational data. It maps to the native JSON column type of your database — jsonb in PostgreSQL, JSON in MySQL — and provides path-based querying, type-safe TypeScript access, and seamless migrations. This guide covers everything from schema definition to raw SQL workarounds for the cases Prisma doesn't handle natively.

Defining Json Fields in Prisma Schema

Declare a field with the built-in Json scalar type in your schema.prisma file. Use Json? for optional JSON columns.

// schema.prisma
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  metadata  Json?            // optional JSON column
  settings  Json             // required JSON column
  createdAt DateTime @default(now())
}

model Product {
  id          String  @id @default(cuid())
  name        String
  price       Float
  attributes  Json    // e.g. { "color": "red", "size": "M", "tags": ["sale"] }
  variants    Json    // array of variant objects
}

Run prisma migrate dev --name add-json-fields to generate and apply the migration. Prisma produces the correct DDL for your provider — for PostgreSQL it generates JSONB, for MySQL JSON, and for SQLite TEXT.

-- PostgreSQL migration generated by Prisma
ALTER TABLE "User"
  ADD COLUMN "metadata" JSONB,
  ADD COLUMN "settings" JSONB NOT NULL;

-- MySQL migration generated by Prisma
ALTER TABLE User
  ADD COLUMN metadata JSON NULL,
  ADD COLUMN settings JSON NOT NULL;

The @db.JsonB native type attribute is not needed — Prisma automatically uses jsonb on PostgreSQL. Only add native type attributes if you need to override to json (non-binary) on PostgreSQL for a specific reason.

Creating Records with JSON Data

Pass plain JavaScript objects and arrays directly to create and createMany. Prisma serializes them to JSON automatically.

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

// Create a user with JSON metadata
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    metadata: {
      role: 'admin',
      department: 'engineering',
      preferences: { theme: 'dark', notifications: true },
    },
    settings: {
      timezone: 'UTC',
      language: 'en',
      features: ['beta-dashboard', 'api-access'],
    },
  },
})

// Create a product with an array of variants
const product = await prisma.product.create({
  data: {
    name: 'Classic T-Shirt',
    price: 29.99,
    attributes: { material: 'cotton', fit: 'regular' },
    variants: [
      { sku: 'TS-S-BLK', size: 'S', color: 'black', stock: 50 },
      { sku: 'TS-M-BLK', size: 'M', color: 'black', stock: 100 },
      { sku: 'TS-L-WHT', size: 'L', color: 'white', stock: 75 },
    ],
  },
})

For optional Json? fields, pass null to write SQL NULL, or Prisma.DbNull for clarity. To explicitly store the JSON literal null (not SQL NULL), use Prisma.JsonNull.

import { Prisma } from '@prisma/client'

// Store SQL NULL (no value)
await prisma.user.create({
  data: { email: 'bob@example.com', settings: {}, metadata: Prisma.DbNull },
})

// Store JSON literal null
await prisma.user.create({
  data: { email: 'carol@example.com', settings: {}, metadata: Prisma.JsonNull },
})

Querying JSON Fields

Prisma supports JSON path filtering for PostgreSQL and MySQL. Use the path array to navigate nested keys and pair it with equals, string_contains, string_starts_with, string_ends_with, array_contains, array_starts_with, or array_ends_with.

// Find all admin users (path into nested key)
const admins = await prisma.user.findMany({
  where: {
    metadata: {
      path: ['role'],
      equals: 'admin',
    },
  },
})

// Find users in the engineering department
const engineers = await prisma.user.findMany({
  where: {
    metadata: {
      path: ['department'],
      string_contains: 'engineering',
    },
  },
})

// Find products with a specific tag (array_contains)
const saleProducts = await prisma.product.findMany({
  where: {
    attributes: {
      path: ['tags'],
      array_contains: 'sale',
    },
  },
})

// Navigate multiple levels deep
const darkThemeUsers = await prisma.user.findMany({
  where: {
    metadata: {
      path: ['preferences', 'theme'],
      equals: 'dark',
    },
  },
})

You can also match records by the entire JSON value using equals with a full object. This performs an exact deep equality check:

// Exact JSON match — rarely useful for complex documents
const users = await prisma.user.findMany({
  where: {
    settings: {
      equals: { timezone: 'UTC', language: 'en', features: ['beta-dashboard', 'api-access'] },
    },
  },
})

Path filtering generates efficient database-level queries. On PostgreSQL, path: ['role'], equals: 'admin' compiles to metadata -> 'role' = '"admin"' or uses the @> containment operator depending on the filter type.

Updating JSON Fields

Prisma does not provide a partial JSON update operator equivalent to MySQL's JSON_SET or PostgreSQL's jsonb_set. Every update replaces the entire JSON value. For partial updates, read the record, merge in JavaScript, and write back.

import type { Prisma } from '@prisma/client'

// Read-modify-write pattern for partial JSON updates
async function updateUserMetadata(
  userId: string,
  patch: Record<string, unknown>,
) {
  const user = await prisma.user.findUniqueOrThrow({
    where: { id: userId },
    select: { metadata: true },
  })

  const current = (user.metadata ?? {}) as Record<string, unknown>

  await prisma.user.update({
    where: { id: userId },
    data: {
      metadata: { ...current, ...patch },
    },
  })
}

// Usage
await updateUserMetadata('user_123', { role: 'editor', lastLogin: new Date().toISOString() })

For deeply nested merges, use a deep-merge utility like lodash.merge instead of the spread operator:

import merge from 'lodash.merge'

const user = await prisma.user.findUniqueOrThrow({ where: { id }, select: { metadata: true } })
const updated = merge({}, user.metadata, { preferences: { theme: 'light' } })

await prisma.user.update({ where: { id }, data: { metadata: updated } })

This read-modify-write approach works fine for most use cases. For high-throughput concurrent updates on the same record, use a raw jsonb_set query (see Section 6) or a database transaction to prevent lost updates.

TypeScript Types for Json Fields

Prisma generates TypeScript types for your schema, but JSON fields use a generic union type. You'll need to narrow or cast these types in your application code.

import type { JsonValue, Prisma } from '@prisma/client'

// JsonValue is: string | number | boolean | null | JsonObject | JsonArray
// JsonObject is: { [key: string]: JsonValue }
// JsonArray is: JsonValue[]

// Define your own typed shape for a specific Json field
interface UserMetadata {
  role: 'admin' | 'editor' | 'viewer'
  department: string
  preferences: {
    theme: 'light' | 'dark'
    notifications: boolean
  }
}

// Cast after reading — Prisma returns JsonValue
const user = await prisma.user.findUniqueOrThrow({ where: { id } })
const metadata = user.metadata as UserMetadata

// Type-safe access
if (metadata.role === 'admin') {
  console.log('Admin from:', metadata.department)
}

// For create/update, use Prisma.InputJsonValue for the data parameter
async function saveSettings(userId: string, settings: Record<string, JsonValue>) {
  await prisma.user.update({
    where: { id: userId },
    data: { settings: settings as Prisma.InputJsonValue },
  })
}

For stricter runtime validation, parse the JSON field with a schema library like Zod after reading from the database:

import { z } from 'zod'

const UserMetadataSchema = z.object({
  role: z.enum(['admin', 'editor', 'viewer']),
  department: z.string(),
  preferences: z.object({
    theme: z.enum(['light', 'dark']),
    notifications: z.boolean(),
  }),
})

const user = await prisma.user.findUniqueOrThrow({ where: { id } })
const metadata = UserMetadataSchema.parse(user.metadata) // throws if shape is wrong
// metadata is now fully typed as z.infer<typeof UserMetadataSchema>

Raw SQL for Complex JSON Operations

For operations Prisma doesn't expose natively — partial updates, JSON aggregation, GIN index queries — use prisma.$queryRaw and prisma.$executeRaw with tagged template literals for safe parameterization.

import { Prisma } from '@prisma/client'

// Partial update with jsonb_set (PostgreSQL)
async function setNestedKey(userId: string, key: string, value: unknown) {
  await prisma.$executeRaw`
    UPDATE "User"
    SET metadata = jsonb_set(
      COALESCE(metadata, '{}'::jsonb),
      ${'{' + key + '}'},
      ${JSON.stringify(value)}::jsonb
    )
    WHERE id = ${userId}
  `
}

// Read with JSON extraction (PostgreSQL)
const rows = await prisma.$queryRaw<Array<{ id: string; role: string }>>`
  SELECT id, metadata->>'role' AS role
  FROM "User"
  WHERE metadata->>'role' IS NOT NULL
`

// Filter using containment operator @> (PostgreSQL — uses GIN index)
const admins = await prisma.$queryRaw<Array<{ id: string; email: string }>>`
  SELECT id, email
  FROM "User"
  WHERE metadata @> '{"role":"admin"}'::jsonb
`

// MySQL JSON_SET for partial update
async function setNestedKeyMySQL(userId: string, path: string, value: string) {
  await prisma.$executeRaw`
    UPDATE User
    SET metadata = JSON_SET(metadata, ${path}, ${value})
    WHERE id = ${userId}
  `
}

Always use the tagged template literal form of $queryRaw and $executeRaw — Prisma parameterizes interpolated values, preventing SQL injection. Never concatenate user input directly into the SQL string.

To add a GIN index for fast @> containment queries on PostgreSQL, add it directly in a migration file (Prisma does not generate GIN index DDL automatically):

-- In a custom migration file
CREATE INDEX user_metadata_gin ON "User" USING GIN (metadata);

JSON vs Separate Columns

Choosing between a Json field and dedicated typed columns is an architectural decision that affects querying, indexing, and schema evolution.

A common hybrid pattern: store your primary queryable fields as typed columns, and overflow dynamic metadata in a Json field. For example, a User model keeps email, role, and planId as indexed columns and stores per-user plugin configuration in a metadata Json field that is never filtered at scale.

Key Terms

JsonValue

The TypeScript union type Prisma uses for values read from Json database columns: string | number | boolean | null | JsonObject | JsonArray. Exported from @prisma/client.

JsonNull

Prisma.JsonNull — a sentinel value used in Prisma create/update calls to store the JSON literal null inside the column (as opposed to SQL NULL). The column is non-null at the SQL level and contains the text null.

DbNull

Prisma.DbNull — a sentinel value that writes SQL NULL into an optional Json? column. Distinct from JsonNull: the column has no value at all rather than containing the JSON text null.

jsonb

PostgreSQL's binary JSON column type. Stores JSON in a decomposed binary format that supports GIN indexing, the @> containment operator, path extraction operators (->, ->>), and faster reads than the plain json type. Prisma maps its Json scalar to jsonb on PostgreSQL by default.

path filter

A Prisma query filter that navigates into a JSON field using a key-path array. Example: { path: ['address', 'city'], equals: 'Berlin' }. Only available for PostgreSQL and MySQL; compiles to native JSON path operators in the generated SQL.

FAQ