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:

Relational databases
MongoDB
model User {
id Int @id @default(autoincrement())
name String
pets 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

Available for:
  • PostgreSQL in versions 2.15.0 and later
  • CockroachDB in versions 3.9.0 and later
  • MongoDB in versions 3.11.0 and later
  • 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

    Available for:
  • PostgreSQL in versions 2.15.0 and later
  • CockroachDB in versions 3.9.0 and later
  • MongoDB in versions 3.11.0 and later
  • 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

    This section applies to:
  • PostgreSQL in versions 2.15.0 and later
  • CockroachDB in versions 3.9.0 and later
  • 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 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