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.

Prerequisites

This is a preview feature. Add the highlighted feature flag to enable distinct querying capabilities:

1generator client {
2 provider = "prisma-client-js"
3 previewFeatures = ["distinct"]
4}

Please help us make this feature production-ready by trying it out, sharing your feedback and reporting bugs! The more and better feedback we receive, the earlier the feature can be released for production usage.

Examples

The following examples are based on a sample schema:

1generator client {
2 provider = "prisma-client-js"
3 experimentalFeatures = ["distinct"]
4}
5
6datasource db {
7 provider = "mysql"
8 url = env("DATABASE_URL")
9}
10
11model Post {
12 authorId Int?
13 content String?
14 id Int @default(autoincrement()) @id
15 published Boolean @default(false)
16 title String
17 user User? @relation(fields: [authorId], references: [id])
18
19 @@index([authorId], name: "authorId")
20}
21
22model User {
23 email String @unique
24 id Int @default(autoincrement()) @id
25 name String?
26 posts Post[]
27 extendedProfile Json?
28 role Role @default(USER)
29 city String?
30 country String?
31}
32
33enum Role {
34 ADMIN
35 USER
36 MODERATOR
37}

Select distinct on a single field

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

1const distinctCities = await prisma.user.findMany({
2 select: {
3 city: true,
4 country: true,
5 },
6 distinct: ["city"],
7});

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:

1const distinctCitiesAndCountries = await prisma.user.findMany({
2 select: {
3 city: true,
4 country: true,
5 },
6 distinct: ["city", "country"],
7});

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:

1const distinctCitiesAndCountries = await prisma.user.findMany({
2 where: {
3 email: {
4 contains: "prisma.io"
5 }
6 },
7 select: {
8 city: true,
9 country: true,
10 },
11 distinct: ["city", "country"],
12});

This is what an example output might look like:

1[
2 { city: 'Paris', country: 'Denmark' },
3 { city: 'Lyon', country: 'France' }
4]

Generated types

The following types are generated for the sample schema:

1export declare const PostDistinctFieldEnum: {
2 authorId: 'authorId',
3 content: 'content',
4 id: 'id',
5 published: 'published',
6 title: 'title'
7};
8
9export declare type PostDistinctFieldEnum = (typeof PostDistinctFieldEnum)[keyof typeof PostDistinctFieldEnum]
10
11export declare const UserDistinctFieldEnum: {
12 email: 'email',
13 id: 'id',
14 name: 'name',
15 extendedProfile: 'extendedProfile',
16 role: 'role',
17 city: 'city',
18 country: 'country'
19};
20
21export declare type UserDistinctFieldEnum = (typeof UserDistinctFieldEnum)[keyof typeof UserDistinctFieldEnum]
Edit this page on Github