Working with scalar lists

Source URL: https://docs.prisma.io/docs/orm/prisma-client/special-fields-and-types/working-with-scalar-lists-arrays

Working with scalar lists

How to read, write, and filter by scalar lists / arrays

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.

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, CockroachDB, and MongoDB.

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, CockroachDB, and MongoDB.

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 and CockroachDB.

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 [].