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:

1model User {
2 email String @unique
3 id Int @default(autoincrement()) @id
4 name String?
5 posts Post[]
6 extendedProfile Json?
7 role Role @default(USER)
8}

The following represents a sample extendedProfile field value:

1{
2 "bio": [
3 {
4 "languages": "Danish, English"
5 },
6 {
7 "age": "87"
8 }
9 ],
10 "pets": "none"
11}

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():

1export declare type JsonValue = string | number | boolean | null | JsonObject | JsonArray

Filtering

Prisma Client supports basic filtering for Json fields. Relevant types:

1export type NullableJsonFilter = {
2 equals?: InputJsonValue | null
3 not?: InputJsonValue | null | NullableJsonFilter
4}

Examples

The examples in this section are based on the following schema:

1generator client {
2 provider = "prisma-client-js"
3}
4
5datasource db {
6 provider = "mysql"
7 url = env("DATABASE_URL")
8}
9
10model Post {
11 authorId Int?
12 content String?
13 id Int @default(autoincrement()) @id
14 published Boolean @default(false)
15 title String
16 user User? @relation(fields: [authorId], references: [id])
17
18 @@index([authorId], name: "authorId")
19}
20
21model User {
22 email String @unique
23 id Int @default(autoincrement()) @id
24 name String?
25 posts Post[]
26 extendedProfile Json?
27 role Role @default(USER)
28}
29
30enum Role {
31 ADMIN
32 USER
33 MODERATOR
34}

Write to a Json field

The following example writes a JSON object to the extendedProfile field:

1var json = { "pets": [{ "name": "Bob the dog" }, { "name": "Claudine the cat" }] };
2
3const createUser = await prisma.user.create({
4 data: {
5 email: "birgitte@prisma.io",
6 extendedProfile: json,
7 },
8});

Note: Javascript objects (for example, { pets: "none"}) are automatically converted to JSON.

Filter on a Json field

1var json = { pets: [{ name: "Sob the dog" }, { name: "Claudine the cat" }] };
2
3const get = await prisma.user.findMany({
4 where: {
5 extendedProfile: {
6 equals: json,
7 },
8 },
9});

The following example omits the equals but produces the same result:

1var json = { pets: [{ name: "Bob the dog" }, { name: "Claudine the cat" }] };
2
3const get = await prisma.user.findMany({
4 where: {
5 extendedProfile: json
6 },
7});

The following example returns all users where the extendedProfile field does not match json:

1var json = { pets: [{ name: "Bob the dog" }, { name: "Claudine the cat" }] };
2
3const get = await prisma.user.findMany({
4 where: {
5 extendedProfile: {
6 not: json,
7 },
8 },
9});
Edit this page on Github