Working with JSON fields
Overview
Use the Json
field to read, write, and perform basic filtering on JSON types in the underlying database. In the following example, the User
model has an optional Json
field named extendedProfile
:
model User {email String @uniqueid Int @default(autoincrement()) @idname String?posts Post[]extendedProfile Json?role Role @default(USER)}
The following represents a sample extendedProfile
field value:
{"bio": [{"languages": "Danish, English"},{"age": "87"}],"pets": "none"}
Note: The
Json
field is only supported if the underlying database has a corresponding JSON data type.
You can use a Json
field if:
- You need to store data that does not adhere to a consistent structure
- You are importing data from another system and do not want to map that data to Prisma models
The Json
field supports a few additional types, such as string
and boolean
. These additional types exist to match the types supported by JSON.parse()
:
export declare type JsonValue = string | number | boolean | null | JsonObject | JsonArray
Filtering
Prisma Client supports basic filtering for Json
fields. Relevant types:
export type NullableJsonFilter = {equals?: InputJsonValue | nullnot?: InputJsonValue | null | NullableJsonFilter}
Examples
The examples in this section are based on the following schema:
generator client {provider = "prisma-client-js"}datasource db {provider = "mysql"url = env("DATABASE_URL")}model Post {authorId Int?content String?id Int @default(autoincrement()) @idpublished Boolean @default(false)title Stringuser User? @relation(fields: [authorId], references: [id])@@index([authorId], name: "authorId")}model User {email String @uniqueid Int @default(autoincrement()) @idname String?posts Post[]extendedProfile Json?role Role @default(USER)}enum Role {ADMINUSERMODERATOR}
Write to a Json
field
The following example writes a JSON
object to the extendedProfile
field:
var json = { "pets": [{ "name": "Bob the dog" }, { "name": "Claudine the cat" }] };const createUser = await prisma.user.create({data: {email: "birgitte@prisma.io",extendedProfile: json,},});
Note: Javascript objects (for example,
{ pets: "none"}
) are automatically converted to JSON.
Filter on a Json
field
var json = { pets: [{ name: "Bob the dog" }, { name: "Claudine the cat" }] };const get = await prisma.user.findMany({where: {extendedProfile: {equals: json,},},});
The following example omits the equals
but produces the same result:
var json = { pets: [{ name: "Bob the dog" }, { name: "Claudine the cat" }] };const get = await prisma.user.findMany({where: {extendedProfile: json},});
The following example returns all users where the extendedProfile
field does not match json
:
var json = { pets: [{ name: "Bob the dog" }, { name: "Claudine the cat" }] };const get = await prisma.user.findMany({where: {extendedProfile: {not: json,},},});