Prisma JSON Field Query: Filter, Update & TypeScript Types

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

Last updated: May 20, 2026

Prisma supports a Json field type that maps to PostgreSQL's jsonb, MySQL's json, SQLite's text, and MongoDB's document — declare it in schema.prisma as metadata Json and Prisma serializes/deserializes automatically. Querying JSON fields differs by database: PostgreSQL supports path and array_contains filter modes; MySQL supports path and string_contains; SQLite has no JSON filter support in Prisma (requires raw SQL). Prisma.JsonNull and Prisma.DbNull are two distinct null values — use Prisma.JsonNull to store JSON null, Prisma.DbNull to store SQL NULL. This guide covers schema declaration, CRUD operations on Json fields, nested filtering with path and array_contains, updating nested fields with $set, type-safe handling with Prisma.JsonValue, and raw SQL fallbacks.

Declaring Json Fields in schema.prisma

Use the Json scalar (capital J) in your Prisma schema to declare a JSON column. Prisma maps this scalar to the native JSON column type of the configured database provider at migration time — you write one schema, and Prisma handles the database-specific DDL. Use Json? for nullable fields, and @default with a quoted JSON string literal to set a column default.

// schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"   // or "mysql" | "sqlite" | "mongodb"
  url      = env("DATABASE_URL")
}

model User {
  id       Int    @id @default(autoincrement())
  email    String @unique
  // Required Json field — database column is NOT NULL
  // Default: empty object stored as jsonb '{}'
  metadata Json   @default("{}")
  // Optional Json field — column can be SQL NULL
  settings Json?
}

model Product {
  id         Int    @id @default(autoincrement())
  name       String
  // Json field for arbitrary key-value attributes
  attributes Json
  // Json field that stores a JSON array — default empty array
  tags       Json   @default("[]")
}

// Apply schema changes:
// npx prisma migrate dev --name add_json_fields
//
// Regenerate client types after schema change:
// npx prisma generate

// Database mappings:
// PostgreSQL  →  jsonb   (binary JSON with GIN index support)
// MySQL       →  JSON    (native JSON column type)
// SQLite      →  TEXT    (stored as JSON string, no native operators)
// MongoDB     →  document field (native BSON subdocument)

The @default value must be a valid JSON string enclosed in double quotes — @default("") for an empty object or @default("[]") for an empty array. Prisma does not support computed or dynamic defaults for Json fields; initialize dynamic defaults in application code before calling create. After any schema change, always run npx prisma generate to update the generated TypeScript types — without this, your client types will be stale and you may encounter type errors or missing type completions when working with the Json field.

Creating and Reading Records with Json Data

Pass any JSON-serializable JavaScript value — object, array, string, number, boolean, or null — as the field value on create or createMany. Prisma serializes the value to the database's native JSON representation. On read, Prisma deserializes the stored JSON back into a JavaScript value typed as Prisma.JsonValue.

import { PrismaClient, Prisma } from '@prisma/client'

const prisma = new PrismaClient()

// CREATE: pass any JSON-serializable value as the field value
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    metadata: {
      name: 'Alice',
      tier: 'pro',
      roles: ['admin', 'editor'],
      address: { city: 'NYC', zip: '10001', country: 'US' },
    },
    settings: {
      theme: 'dark',
      notifications: true,
      language: 'en',
    },
  },
})
// user.metadata is typed as Prisma.JsonValue

// READ: findUnique returns the Json field as Prisma.JsonValue
const found = await prisma.user.findUnique({
  where: { id: user.id },
})

// SELECT specific fields — reduces payload size on large Json columns
const users = await prisma.user.findMany({
  select: { id: true, email: true, metadata: true },
})

// findMany: exact whole-value equality filter on a Json field
const proUsers = await prisma.user.findMany({
  where: {
    metadata: {
      equals: { name: 'Alice', tier: 'pro', roles: ['admin', 'editor'] },
    },
  },
})

// COUNT records where the optional Json? field has a SQL value (not DbNull)
const withSettings = await prisma.user.count({
  where: { settings: { not: Prisma.DbNull } },
})

// CREATE with a Json array field
const product = await prisma.product.create({
  data: {
    name: 'Widget',
    attributes: { color: 'red', weight: 1.5, inStock: true },
    tags: ['sale', 'featured', 'new'],
  },
})

The equals filter on a Json field performs a deep whole-value equality check — it matches rows where the stored JSON is identical to the provided value. For nested key lookups (e.g., find users where metadata.tier === 'pro'), use the path filter described in the next section. The select option is especially important for tables with large JSON blobs — fetching only the fields your query needs avoids transmitting unnecessary data over the network.

Filtering Json Fields: path, string_contains, array_contains

Prisma's JSON path filter navigates a nested JSON document by a sequence of string keys and then applies an operator. On PostgreSQL JSONB, Prisma generates a #>> path operator or a @> containment operator. On MySQL, it generates a JSON_EXTRACT call. All path operators are fully supported on both PostgreSQL and MySQL; SQLite support is limited — most operators require $queryRaw.

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

// path + equals: match an exact value at a JSON key
const proUsers = await prisma.user.findMany({
  where: {
    metadata: { path: ['tier'], equals: 'pro' },
  },
})

// Nested path: metadata.address.city === 'NYC'
const nycUsers = await prisma.user.findMany({
  where: {
    metadata: { path: ['address', 'city'], equals: 'NYC' },
  },
})

// string_contains: metadata.name contains 'alice' (case-sensitive)
const aliceUsers = await prisma.user.findMany({
  where: {
    metadata: { path: ['name'], string_contains: 'alice' },
  },
})

// string_starts_with: metadata.tier starts with 'pr'
const prUsers = await prisma.user.findMany({
  where: {
    metadata: { path: ['tier'], string_starts_with: 'pr' },
  },
})

// string_ends_with: metadata.email ends with '@example.com'
const exampleUsers = await prisma.user.findMany({
  where: {
    metadata: { path: ['email'], string_ends_with: '@example.com' },
  },
})

// array_contains: metadata.roles array includes 'admin'
// array_contains is a subset check — ['admin'] matches rows where
// the stored roles array contains 'admin' among other elements
const adminUsers = await prisma.user.findMany({
  where: {
    metadata: { path: ['roles'], array_contains: ['admin'] },
  },
})

// array_contains on a top-level Json array field (no path needed)
const featuredProducts = await prisma.product.findMany({
  where: {
    tags: { array_contains: ['featured'] },
  },
})

// Combine Json filter with other field filters
const activeAdmins = await prisma.user.findMany({
  where: {
    email: { endsWith: '@company.com' },
    metadata: { path: ['roles'], array_contains: ['admin'] },
  },
})

The path array mirrors key traversal depth — ['address', 'city'] navigates to metadata.address.city. For array indexes, use a numeric string: ['roles', '0'] targets the first element of the roles array. Note that path filters run a JSON scan on the column; on large tables, PostgreSQL GIN indexes on jsonb columns (CREATE INDEX ON "User" USING gin(metadata)) significantly speed up @> containment queries but do not accelerate #>> path equality queries — for those, consider a generated column index.

Updating Nested Json Values with $set

Prisma's standard update replaces the entire Json field value. For partial key updates without a full replacement, Prisma exposes a $set operator on PostgreSQL and MySQL that performs an atomic in-database nested update — similar to MongoDB's $set semantics. This avoids the read-then-write round-trip of the spread-merge pattern.

import { PrismaClient, Prisma } from '@prisma/client'
const prisma = new PrismaClient()

// Full replacement: write a new object, discarding all previous keys
await prisma.user.update({
  where: { id: 1 },
  data: {
    metadata: { name: 'Alice', tier: 'pro', roles: ['admin'] },
  },
})

// $set operator: atomically update specific nested keys
// Supported on PostgreSQL (jsonb_set) and MySQL (JSON_SET)
// Does NOT require a read round-trip
await prisma.user.update({
  where: { id: 1 },
  data: {
    metadata: {
      $set: { tier: 'enterprise' },   // update tier only; other keys preserved
    },
  },
})

// $set with multiple keys in one operation
await prisma.user.update({
  where: { id: 1 },
  data: {
    metadata: {
      $set: {
        tier: 'enterprise',
        verified: true,
        updatedAt: new Date().toISOString(),
      },
    },
  },
})

// Spread-merge pattern: read, spread, overwrite (database-agnostic fallback)
const user = await prisma.user.findUniqueOrThrow({ where: { id: 1 } })
const currentMetadata = user.metadata as Prisma.JsonObject
await prisma.user.update({
  where: { id: 1 },
  data: {
    metadata: {
      ...currentMetadata,
      tier: 'pro',
      lastLogin: new Date().toISOString(),
    },
  },
})

// Set to JSON null (stores JSON null, not SQL NULL)
await prisma.user.update({
  where: { id: 1 },
  data: { metadata: Prisma.JsonNull },
})

// Set nullable Json? field to SQL NULL
await prisma.user.update({
  where: { id: 1 },
  data: { settings: Prisma.DbNull },
})

// $executeRaw fallback for PostgreSQL jsonb_set (deepest nested update)
await prisma.$executeRaw`
  UPDATE "User"
  SET metadata = jsonb_set(metadata, '{address,city}', '"London"')
  WHERE id = ${1}
`

The $set operator is the most efficient approach for partial updates on PostgreSQL and MySQL — it translates to a single jsonb_set or JSON_SET call with no extra network round-trip. The spread-merge pattern is database-agnostic and works on SQLite, but it requires two database operations (read + write) and is not safe under concurrent writes — two simultaneous updates can silently overwrite each other's changes. For high-concurrency scenarios, always prefer $set or $executeRaw with atomic SQL functions.

Prisma.JsonNull vs Prisma.DbNull: Two Kinds of Null

Prisma distinguishes two null values for Json fields because databases treat them differently: JSON null is a valid JSON value stored inside the column, while SQL NULL means the column is absent. Both surface as JavaScript null when read back, but they have different storage semantics, different query behavior, and different write sentinels in Prisma.

import { PrismaClient, Prisma } from '@prisma/client'
const prisma = new PrismaClient()

// ── Write semantics ────────────────────────────────────────────────

// Prisma.JsonNull: store the JSON literal null in the column
// The column has a value — that value is JSON null
// SQL equivalent: UPDATE "User" SET settings = 'null'::jsonb WHERE id = 1
await prisma.user.update({
  where: { id: 1 },
  data: { settings: Prisma.JsonNull },
})

// Prisma.DbNull: store SQL NULL in the column
// The column has no value at all
// SQL equivalent: UPDATE "User" SET settings = NULL WHERE id = 1
// Only valid on nullable Json? fields
await prisma.user.update({
  where: { id: 1 },
  data: { settings: Prisma.DbNull },
})

// ── Read semantics ─────────────────────────────────────────────────

// Both Prisma.JsonNull and Prisma.DbNull read back as JavaScript null
// You cannot distinguish them from the returned value alone
const user1 = await prisma.user.findUniqueOrThrow({ where: { id: 1 } })
console.log(user1.settings)  // null  — could be JsonNull OR DbNull

// ── Filter semantics ───────────────────────────────────────────────

// Filter for rows with SQL NULL (DbNull) in settings
const dbNullUsers = await prisma.user.findMany({
  where: { settings: Prisma.DbNull },
})

// Filter for rows with JSON null (JsonNull) in settings
const jsonNullUsers = await prisma.user.findMany({
  where: { settings: Prisma.JsonNull },
})

// Filter for rows where settings is EITHER null type (AnyNull)
const anyNullUsers = await prisma.user.findMany({
  where: { settings: Prisma.AnyNull },
})

// Filter for rows where settings is NOT null (has a real JSON value)
const withSettings = await prisma.user.findMany({
  where: { settings: { not: Prisma.AnyNull } },
})

// ── Common pitfall: using JavaScript null instead of Prisma.JsonNull ──
// WRONG: this causes a TypeScript type error
// await prisma.user.update({ data: { settings: null } })  // TypeError

// CORRECT: use the appropriate sentinel
await prisma.user.update({
  where: { id: 1 },
  data: { settings: Prisma.DbNull },  // SQL NULL
})

The Prisma.AnyNull sentinel is useful in filter clauses when you want to match rows regardless of which kind of null is stored — it generates an IS NULL OR value = null::jsonb condition. In write operations, you must always use either Prisma.JsonNull or Prisma.DbNull — passing a plain JavaScript null will produce a TypeScript type error. If you only ever want SQL NULL semantics (the column is empty or not), use a nullable Json? field and always write with Prisma.DbNull — this avoids the ambiguity entirely.

Type Safety with Prisma.JsonValue and Custom Types

Prisma generates Prisma.JsonValue as the read type for all Json fields — a deeply recursive union that TypeScript cannot narrow automatically. Accessing nested keys requires a cast or a type-narrowing function. The write type is Prisma.InputJsonValue, which additionally accepts the null sentinels. Use Zod schemas to validate and narrow JsonValue results at runtime.

import { PrismaClient, Prisma } from '@prisma/client'
import { z } from 'zod'

const prisma = new PrismaClient()

// Prisma.JsonValue — the complete union type (defined by Prisma):
// type JsonValue = string | number | boolean | null
//   | JsonValue[]
//   | { [key: string]: JsonValue }

// ── Cast to Prisma.JsonObject for object access ────────────────────
const user = await prisma.user.findUniqueOrThrow({ where: { id: 1 } })

// Bad: casts to any — no type safety
// const tier = (user.metadata as any).tier

// Good: cast through Prisma.JsonObject, then narrow each value
const metadata = user.metadata as Prisma.JsonObject
const tier      = metadata['tier'] as string        // still requires narrowing
const roles     = metadata['roles'] as string[]     // still requires narrowing

// ── Type narrowing function (no runtime assumptions) ───────────────
function getString(value: Prisma.JsonValue): string | undefined {
  return typeof value === 'string' ? value : undefined
}

function getObject(value: Prisma.JsonValue): Prisma.JsonObject | undefined {
  if (typeof value === 'object' && value !== null && !Array.isArray(value)) {
    return value as Prisma.JsonObject
  }
  return undefined
}

// Usage: fully type-safe access
const obj = getObject(user.metadata)
const safeTier = obj ? getString(obj['tier']) : undefined

// ── Zod schema for runtime validation and type inference ───────────
const MetadataSchema = z.object({
  name:    z.string(),
  tier:    z.enum(['free', 'pro', 'enterprise']),
  roles:   z.array(z.string()),
  address: z.object({
    city:    z.string(),
    zip:     z.string(),
    country: z.string(),
  }).optional(),
})

type Metadata = z.infer<typeof MetadataSchema>

// Parse the Prisma.JsonValue through Zod — throws if shape does not match
const parsedMetadata: Metadata = MetadataSchema.parse(user.metadata)
console.log(parsedMetadata.tier)    // string, typed as 'free' | 'pro' | 'enterprise'
console.log(parsedMetadata.roles)   // string[], fully typed

// ── Prisma.InputJsonValue — for write operations ───────────────────
// Use this type when accepting a JSON value as a function parameter
function updateMetadataTier(
  userId: number,
  tier: string
): Promise<unknown> {
  const patch: Prisma.InputJsonValue = { $set: { tier } }
  return prisma.user.update({
    where: { id: userId },
    data: { metadata: patch },
  })
}

// ── Type-safe helper wrapping the common read-cast pattern ─────────
async function getUserMetadata(id: number): Promise<Metadata> {
  const user = await prisma.user.findUniqueOrThrow({ where: { id } })
  return MetadataSchema.parse(user.metadata)
}

The Zod approach is the most robust pattern for production code: it validates that the database actually contains the shape your application expects (guarding against schema drift), provides full TypeScript type inference on the parsed result, and gives descriptive errors when the shape does not match. See the TypeScript JSON patterns and JSON schema validation guides for advanced Zod integration patterns.

Raw SQL Fallbacks for Complex JSON Queries

Prisma's JSON filter API covers the most common cases, but some queries require raw SQL: SQLite JSON filtering, advanced PostgreSQL JSONB path expressions, MongoDB JSON document aggregations, JSON_TABLE in MySQL, and atomic array manipulation functions. Use prisma.$queryRaw for queries that return typed results and prisma.$executeRaw for mutations.

import { PrismaClient, Prisma } from '@prisma/client'
const prisma = new PrismaClient()

// ── $queryRaw: typed raw SELECT (PostgreSQL jsonb operators) ───────
type UserRow = { id: number; email: string; tier: string }

const proUsers = await prisma.$queryRaw<UserRow[]>`
  SELECT id, email, metadata->>'tier' AS tier
  FROM "User"
  WHERE metadata->>'tier' = ${1}
`('pro')

// ── $queryRaw: nested path with #>> operator ───────────────────────
const nycUsers = await prisma.$queryRaw<{ id: number }[]>`
  SELECT id FROM "User"
  WHERE metadata#>>'{address,city}' = ${1}
`('NYC')

// ── $queryRaw: array containment with @> operator (uses GIN index) ─
const adminUsers = await prisma.$queryRaw<{ id: number; email: string }[]>`
  SELECT id, email FROM "User"
  WHERE metadata->'roles' @> '["admin"]'::jsonb
`

// ── SQLite: json_extract() via $queryRaw ──────────────────────────
// (SQLite does not support path filters in Prisma — use $queryRaw)
const sqliteUsers = await prisma.$queryRaw<{ id: number }[]>`
  SELECT id FROM User
  WHERE json_extract(metadata, '$.tier') = ${1}
`('pro')

// ── $executeRaw: atomic nested update (PostgreSQL jsonb_set) ───────
await prisma.$executeRaw`
  UPDATE "User"
  SET metadata = jsonb_set(metadata, '{address,city}', '"Tokyo"')
  WHERE id = ${1}
`

// ── $executeRaw: atomic array append (PostgreSQL jsonb_insert) ─────
await prisma.$executeRaw`
  UPDATE "Product"
  SET tags = jsonb_insert(tags, '{-1}', '"clearance"'::jsonb, true)
  WHERE id = ${1}
`

// ── MySQL equivalents ──────────────────────────────────────────────
// JSON_EXTRACT for reads:
// SELECT id FROM User WHERE JSON_EXTRACT(metadata, '$.tier') = 'pro'
//
// JSON_SET for nested update:
// UPDATE User SET metadata = JSON_SET(metadata, '$.tier', 'pro') WHERE id = 1
//
// JSON_ARRAY_APPEND for atomic append:
// UPDATE Product SET tags = JSON_ARRAY_APPEND(tags, '$', 'clearance') WHERE id = 1

// ── $queryRaw tag literal: always use template literals, never string concat ──
// WRONG (SQL injection risk):
// await prisma.$queryRawUnsafe(`SELECT * FROM "User" WHERE metadata->>'tier' = '${tier}'`)

// CORRECT (parameterized, safe):
const tier = 'pro'
const safe = await prisma.$queryRaw`
  SELECT id FROM "User" WHERE metadata->>'tier' = ${tier}
`

Always use template literals (backtick syntax) with $queryRaw and $executeRaw — Prisma interpolates template literal expressions as parameterized placeholders, preventing SQL injection. Never use $queryRawUnsafe with user-supplied input; reserve it for schema-level operations like CREATE INDEX where parameterization is not possible. For PostgreSQL, creating a GIN index on the jsonb column (CREATE INDEX CONCURRENTLY ON "User" USING gin(metadata)) accelerates containment queries (@>) by orders of magnitude on large tables — profile your queries with EXPLAIN ANALYZE before and after adding the index.

Key Terms

Json field type

The Prisma schema scalar keyword for a JSON column, written as Json (capital J) in schema.prisma. Prisma maps it to the database's native JSON column type at migration time: PostgreSQL jsonb, MySQL JSON, SQLite TEXT, and MongoDB document. The Json scalar accepts any JSON-serializable value — object, array, string, number, boolean, or null (via sentinels). It does not support @unique or relational constraints. Add ? for a nullable field (Json?) and @default with a quoted JSON string for a column default.

Prisma.JsonValue

The TypeScript type Prisma generates for the read value of a Json field. It is a recursive union: string | number | boolean | null | Prisma.JsonValue[] | { [key: string]: Prisma.JsonValue }. Because it is a union, TypeScript cannot narrow it automatically — you must cast or use a type guard before accessing nested properties. Use Prisma.JsonObject (an alias for { [key: string]: Prisma.JsonValue }) to cast to an object, and Prisma.JsonArray (an alias for Prisma.JsonValue[]) to cast to an array. The counterpart for write operations is Prisma.InputJsonValue.

Prisma.JsonNull

A Prisma sentinel value used in write operations to store the JSON literal null in a Json or Json? column. When you pass Prisma.JsonNull as a field value in create or update, Prisma writes the JSON value null to the database column — the column has a value, and that value is JSON null. This differs from SQL NULL (Prisma.DbNull) where the column has no value. Both sentinel types read back as JavaScript null; distinguish them in queries using Prisma.JsonNull, Prisma.DbNull, or Prisma.AnyNull in where filters.

Prisma.DbNull

A Prisma sentinel value used in write operations to store SQL NULL in a nullable Json? column. When you pass Prisma.DbNull, Prisma writes a SQL NULL — the column has no value at all, semantically different from the JSON literal null. Prisma.DbNull is only valid on Json? (nullable) fields; passing it on a required Json field causes a runtime error. Both Prisma.JsonNull and Prisma.DbNull surface as JavaScript null when read, so use Prisma.AnyNull in a where filter to match either kind of null.

path filter

A Prisma JSON query construct that navigates a nested JSON document by an array of key names before applying an operator. Expressed as { path: ['key1', 'key2'], equals: 'value' } inside a where clause on a Json field. The path array mirrors key traversal depth — ['address', 'city'] reaches field.address.city. Available operators: equals, string_contains, string_starts_with, string_ends_with, array_contains, array_starts_with, array_ends_with. Prisma compiles path filters to native database JSON operators; fully supported on PostgreSQL and MySQL, limited on SQLite.

queryRaw

A Prisma client method (prisma.$queryRaw) that executes a raw SQL SELECT and returns typed results. Use it as a fallback for JSON queries the Prisma filter API cannot express — SQLite json_extract() lookups, PostgreSQL jsonpath expressions, MySQL JSON_TABLE, or complex aggregations. Always use the tagged template literal syntax — prisma.$queryRaw`SELECT ... WHERE col = ${value}` — which parameterizes interpolated values and prevents SQL injection. Never use $queryRawUnsafe with user-supplied input. The counterpart for mutations is prisma.$executeRaw.

FAQ