Prisma Client allows you to filter duplicate rows from a Prisma Query response to a findMany query using distinct.

distinct is often used in combination with select to identify certain unique combinations of values in the rows of your table.

Examples

The following examples are based on a 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)
city String?
country String?
}
enum Role {
ADMIN
USER
MODERATOR
}

Select distinct on a single field

The following example returns all distinct city fields, and selects only the city and country fields:

const distinctCities = await prisma.user.findMany({
select: {
city: true,
country: true,
},
distinct: ["city"],
});

Example output:

[
{ city: 'Paris', country: 'France' },
{ city: 'Lyon', country: 'France' }
]

Select distinct on multiple fields

The following example returns all distinct city and country field combinations, and selects only the city and country fields:

const distinctCitiesAndCountries = await prisma.user.findMany({
select: {
city: true,
country: true,
},
distinct: ["city", "country"],
});

Here is what an example output might look like - note that there is now a "Paris, Denmark" in addition to "Paris, France":

[
{ city: 'Paris', country: 'France' },
{ city: 'Paris', country: 'Denmark' },
{ city: 'Lyon', country: 'France' }
]

Select distinct in combination with a filter

The following example returns all distinct city and country field combinations where the user's email contains "prisma.io", and selects only the city and country fields:

const distinctCitiesAndCountries = await prisma.user.findMany({
where: {
email: {
contains: "prisma.io"
}
},
select: {
city: true,
country: true,
},
distinct: ["city", "country"],
});

This is what an example output might look like:

[
{ city: 'Paris', country: 'Denmark' },
{ city: 'Lyon', country: 'France' }
]

Generated types

The following types are generated for the sample schema:

export declare const PostDistinctFieldEnum: {
authorId: 'authorId',
content: 'content',
id: 'id',
published: 'published',
title: 'title'
};
export declare type PostDistinctFieldEnum = (typeof PostDistinctFieldEnum)[keyof typeof PostDistinctFieldEnum]
export declare const UserDistinctFieldEnum: {
email: 'email',
id: 'id',
name: 'name',
extendedProfile: 'extendedProfile',
role: 'role',
city: 'city',
country: 'country'
};
export declare type UserDistinctFieldEnum = (typeof UserDistinctFieldEnum)[keyof typeof UserDistinctFieldEnum]
Edit this page on GitHub