Working with scalar lists
Scalar lists are represented by the []
modifier and are only available if the underlying database supports scalar lists. The following example has one scalar String
list named pets
:
model User {id Int @id @default(autoincrement())name Stringpets String[]}
Example field value:
['Fido', 'Snoopy', 'Brian']
Setting the value of a scalar list
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],},})
Unsetting the value of a scalar list
This method is available on MongoDB only in versions 3.11.1 and later.
The following example demonstrates how to unset
the value of a scalar list (coinflips
):
const createdUser = await prisma.user.create({data: {email: 'eloise@prisma.io',coinflips: {unset: true,},},})
Unlike set: null
, unset
removes the list entirely.
Adding items to a scalar list
Use the push
method to add a single value to a scalar list:
const userUpdate = await prisma.user.update({where: {id: 9,},data: {coinflips: {push: true,},},})
In earlier versions, you have to 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
Use scalar list filters to filter for records with scalar lists that match a specific condition. 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
When using scalar list filters with a relational database connector, array fields with a NULL
value are not considered by the 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 includedatabases
: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"
- ✔ Arrays that do not contain
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 []
.