Skip to main content

Select fields

By default, when a query returns records (as opposed to a count), the result includes the default selection set:

  • All scalar fields defined in the Prisma schema (including enums)
  • None of the relations

To customize the result:

Selecting only the fields and relations that you require rather than relying on the default selection set can ✔ reduce the size of the response and ✔ improve query speed.

Since version 5.9.0, when doing a relation query with include or by using select on a relation field, you can also specify the relationLoadStrategy to decide whether you want to use a database-level join or perform multiple queries and merge the data on the application level. This feature is currently in Preview, you can learn more about it here.

Example schema

All examples are based on the following schema:

Expand for sample schema
generator client {
provider = "prisma-client-js"
}

datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}

model ExtendedProfile {
id Int @id @default(autoincrement())
biography String
user User @relation(fields: [userId], references: [id])
userId Int @unique
}

model User {
id Int @id @default(autoincrement())
name String?
email String @unique
profileViews Int @default(0)
role Role @default(USER)
coinflips Boolean[]
posts Post[]
profile ExtendedProfile?
}

model Post {
id Int @id @default(autoincrement())
title String
published Boolean @default(true)
author User @relation(fields: [authorId], references: [id])
authorId Int
comments Json?
views Int @default(0)
likes Int @default(0)
categories Category[]
}

model Category {
id Int @id @default(autoincrement())
name String @unique
posts Post[]
}

enum Role {
USER
ADMIN
}

For relational databases, use db push command to push the example schema to your own database

npx prisma db push

For MongoDB, ensure your data is in a uniform shape and matches the model defined in the Prisma schema.

Return the default selection set

The following query returns the default selection set (all scalar fields, no relations):

// Query returns User or null
const getUser: User | null = await prisma.user.findUnique({
where: {
id: 22,
},
})
Show query results
{
id: 22,
name: "Alice",
email: "alice@prisma.io",
profileViews: 0,
role: "ADMIN",
coinflips: [true, false],
}

Select specific fields

Use select to return a limited subset of fields instead of all fields. The following example returns the email and name fields only:

// Returns an object or null
const getUser: object | null = await prisma.user.findUnique({
where: {
id: 22,
},
select: {
email: true,
name: true,
},
})
Show query results
{
name: "Alice",
email: "alice@prisma.io",
}

Include relations and select relation fields

To return specific relation fields, you can:

  • Use a nested select
  • Use a select within an include

To return all relation fields, use include only - for example, { include: { posts: true } }.

The following query uses a nested select to select each user's name and the title of each related post:

const users = await prisma.user.findMany({
select: {
name: true,
posts: {
select: {
title: true,
},
},
},
})
Show query results
{
"name":"Sabelle",
"posts":[
{
"title":"Getting started with Azure Functions"
},
{
"title":"All about databases"
}
]
}

The following query uses select within an include, and returns all user fields and each post's title field:

const users = await prisma.user.findMany({
// Returns all user fields
include: {
posts: {
select: {
title: true,
},
},
},
})
Show query results
{
"id": 9
"name": "Sabelle",
"email": "sabelle@prisma.io",
"profileViews": 90,
"role": "USER",
"profile": null,
"coinflips": [],
"posts":[
{
"title":"Getting started with Azure Functions"
},
{
"title":"All about databases"
}
]
}

For more information about querying relations, refer to the following documentation:

Relation count

In 3.0.1 and later, you can include or select a count of relations alongside fields - for example, a user's post count.