Working with JSON fields (Reference)

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  @unique
  id              Int     @default(autoincrement()) @id
  name            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 | null
  not?: InputJsonValue | null | NullableJsonFilter
}

Examples

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

Expand sample 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()) @id
  published Boolean @default(false)
  title     String
  user      User?   @relation(fields: [authorId], references: [id])
  @@index([authorId], name: "authorId")
}
model User {
  email           String  @unique
  id              Int     @default(autoincrement()) @id
  name            String?
  posts           Post[]
  extendedProfile Json?
  role            Role    @default(USER)
}
enum Role {
  ADMIN
  USER
  MODERATOR
}

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,
    },
  },
});

Was this helpful?

Edit this page on GitHub

Working with JSON fields

Overview

Filtering

Examples

Write to a `Json` field

Filter on a `Json` field

Was this helpful?

Products

Resources

Prisma With

Community

Company

Newsletter

Find Us

Working with JSON fields

Overview

Filtering

Examples

Write to a Json field

Filter on a Json field

Was this helpful?

Products

Resources

Prisma With

Community

Company

Newsletter

Find Us

Write to a `Json` field

Filter on a `Json` field