Working with field types

This section describes how to work with field types like Json and lists/arrays of scalars.

Working with Decimal

The following model uses the three new Prisma types:

model sample {
id Int @id @default(autoincrement()) @db.Integer
decimal Decimal? @db.Numeric
}

The following example demonstrates how to create and filter on sample records with Prisma Client:

import { PrismaClient, Prisma } from '@prisma/client'
// Create record
const newTypes = await prisma.sample.create({
data: {
bigInt: BigInt(534543543534),
decimal: new Prisma.Decimal(24.454545),
byteA: Buffer.from(['Hello', 'this is a string', 1, 9, 4]),
},
})
// Filter records
const newTypesFilter = await prisma.dates.findMany({
where: {
bigInt: {
gt: BigInt(4594375943753),
},
byteA: {
not: Buffer.from(['Hey!']),
},
},
})

Working with BigInt

The following model uses the three new Prisma types:

model sample {
id Int @id @default(autoincrement()) @db.Integer
decimal Decimal? @db.Numeric
}

The following example demonstrates how to create and filter on sample records with Prisma Client:

import { PrismaClient, Prisma } from '@prisma/client'
// Create record
const newTypes = await prisma.sample.create({
data: {
bigInt: BigInt(534543543534),
decimal: new Prisma.Decimal(24.454545),
byteA: Buffer.from(['Hello', 'this is a string', 1, 9, 4]),
},
})
// Filter records
const newTypesFilter = await prisma.dates.findMany({
where: {
bigInt: {
gt: BigInt(4594375943753),
},
byteA: {
not: Buffer.from(['Hey!']),
},
},
})

Working with Json

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:

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

Working with scalar lists / scalar arrays

The following example demonstrates how to set the value of a scalar list (coinflips) when you create a model:

const createdUser = await prisma.user.create({
data: {
email: 'eloise@prisma.io',
coinflips: [true, true, true, false, true],
},
})

Scalar lists only support setting a value - you cannot add or remove elements, only overwrite the entire value. The following example retrieves user, uses push() to add three new coin flips, and overwrites the coinflips field in an update:

const user = await prisma.user.findUnique({
where: {
email: 'eloise@prisma.io',
},
})
if (user) {
console.log(user.coinflips)
user.coinflips.push(true, true, false)
const updatedUser = await prisma.user.update({
where: {
email: 'eloise@prisma.io',
},
data: {
coinflips: user.coinflips,
},
})
console.log(updatedUser.coinflips)
}

Filtering scalar lists

In 2.15.0 and later (Postgres only), you can filter scalar lists - for example, a list of String or Enum types. The following example returns all posts where the tags list includes databases and typescript:

const posts = await prisma.post.findMany({
where: {
tags: {
hasEvery: ['databases', 'typescript'],
},
},
})

NULL values in arrays

Array fields with a NULL value are not considered by following conditions:

  • NOT (array does not contain X)
  • isEmpty (array is empty)

This means that records you might expect to see are not returned. Consider the following examples:

  • The following query returns all posts where the tags do not include databases:

    const posts = await prisma.post.findMany({
    where: {
    NOT: {
    tags: {
    has: 'databases',
    },
    },
    },
    })
    • ✔ Arrays that do not contain "databases", such as {"typescript", "graphql"}
    • ✔ Empty arrays, such as {}

    The query does not return:

    • NULL arrays, even though they do not contain "databases"

The following query returns all posts where tags is empty:

const posts = await prisma.post.findMany({
where: {
tags: {
isEmpty: true,
},
},
})

The query returns:

  • ✔ Empty arrays, such as {}

The query does not return:

  • NULL arrays, even though they could be considered empty

To work around this issue, you can set the default value of array fields to {} at a database level (Prisma schema does not currently support default values for arrays.)

Edit this page on GitHub