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,
  },
})

{
  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,
  },
})

{
  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,
      },
    },
  },
})

{
   "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,
      },
    },
  },
})

{
  "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.