Last Updated: 3/20/2026

Relations (JSON Nesting)

Kysely is not an ORM and doesn’t have the concept of relations. However, you can fetch related rows in a single query using JSON aggregation helpers. These helpers use database-specific JSON functions to nest related data as JSON arrays or objects.

The Helpers

Kysely provides dialect-specific helpers in src/helpers/:

jsonArrayFrom() — Aggregates a subquery into a JSON array.
jsonObjectFrom() — Turns a subquery into a JSON object (expects one row).
jsonBuildObject() — Builds a JSON object from expressions.

Each dialect has its own implementation because the underlying SQL functions differ:

PostgreSQL uses json_agg() and to_json()
MySQL uses json_arrayagg() and json_object()
SQLite uses json_group_array() and json_object()

Use jsonArrayFrom() to fetch related rows as a JSON array:


import { jsonArrayFrom } from 'kysely/helpers/postgres'
 
const result = await db
  .selectFrom('person')
  .select((eb) => [
    'id',
    'first_name',
    jsonArrayFrom(
      eb.selectFrom('pet')
        .select(['pet.id as pet_id', 'pet.name'])
        .whereRef('pet.owner_id', '=', 'person.id')
        .orderBy('pet.name')
    ).as('pets')
  ])
  .execute()
 
console.log(result[0]?.pets[0]?.pet_id)
console.log(result[0]?.pets[0]?.name)

The generated SQL (PostgreSQL):


select "id", "first_name", (
  select coalesce(json_agg(agg), '[]') from (
    select "pet"."id" as "pet_id", "pet"."name"
    from "pet"
    where "pet"."owner_id" = "person"."id"
    order by "pet"."name"
  ) as agg
) as "pets"
from "person"

The result type is fully inferred:


// result[0] has type:
{
  id: number
  first_name: string
  pets: Array<{
    pet_id: number
    name: string
  }>
}

Use jsonObjectFrom() to fetch a single related row as a JSON object:


import { jsonObjectFrom } from 'kysely/helpers/postgres'
 
const result = await db
  .selectFrom('person')
  .select((eb) => [
    'id',
    'first_name',
    jsonObjectFrom(
      eb.selectFrom('pet')
        .select(['pet.id as pet_id', 'pet.name'])
        .whereRef('pet.owner_id', '=', 'person.id')
        .where('pet.is_favorite', '=', true)
    ).as('favorite_pet')
  ])
  .execute()
 
console.log(result[0]?.favorite_pet?.pet_id)
console.log(result[0]?.favorite_pet?.name)

The generated SQL (PostgreSQL):


select "id", "first_name", (
  select to_json(obj) from (
    select "pet"."id" as "pet_id", "pet"."name"
    from "pet"
    where "pet"."owner_id" = "person"."id"
    and "pet"."is_favorite" = $1
  ) as obj
) as "favorite_pet"
from "person"

The result type includes null because the subquery might not return a row:


// result[0] has type:
{
  id: number
  first_name: string
  favorite_pet: {
    pet_id: number
    name: string
  } | null
}

Dialect Differences

The helpers work the same way across dialects, but the generated SQL differs:

PostgreSQL


import { jsonArrayFrom } from 'kysely/helpers/postgres'

Uses json_agg() and to_json(). Automatically parses JSON into JavaScript objects.

MySQL


import { jsonArrayFrom } from 'kysely/helpers/mysql'

Uses json_arrayagg() and json_object(). Automatically parses JSON into JavaScript objects with the built-in MysqlDialect.

Note: MySQL’s json_object() function doesn’t support selectAll(). You must explicitly select columns:


// ❌ This will throw an error
jsonArrayFrom(
  eb.selectFrom('pet').selectAll().whereRef('pet.owner_id', '=', 'person.id')
)
 
// ✅ This works
jsonArrayFrom(
  eb.selectFrom('pet')
    .select(['pet.id', 'pet.name'])
    .whereRef('pet.owner_id', '=', 'person.id')
)

SQLite


import { jsonArrayFrom } from 'kysely/helpers/sqlite'

Uses json_group_array() and json_object(). Automatically parses JSON into JavaScript objects with the built-in SqliteDialect.

ParseJSONResultsPlugin

Some third-party dialects may return JSON columns as strings instead of parsing them automatically. In these cases, use the ParseJSONResultsPlugin (see src/plugin/parse-json-results/parse-json-results-plugin.ts):


import { ParseJSONResultsPlugin } from 'kysely'
 
const db = new Kysely<Database>({
  dialect: myDialect,
  plugins: [new ParseJSONResultsPlugin()]
})

This plugin walks the result rows and parses any string that looks like JSON.

The `$notNull()` Helper

When you know a jsonObjectFrom() result will never be null, use the $notNull() helper to refine the type:


import { jsonObjectFrom } from 'kysely/helpers/postgres'
 
const result = await db
  .selectFrom('person')
  .select((eb) => [
    'id',
    jsonObjectFrom(
      eb.selectFrom('pet')
        .select(['pet.id as pet_id', 'pet.name'])
        .whereRef('pet.owner_id', '=', 'person.id')
        .limit(1)
    ).$notNull().as('first_pet')
  ])
  .execute()
 
// result[0].first_pet is typed as { pet_id: number; name: string }
// (not null)

Warning: This is a type-level assertion. If the subquery actually returns null, you’ll get a runtime error.

Building JSON Objects from Expressions

Use jsonBuildObject() to build a JSON object from individual expressions:


import { sql } from 'kysely'
import { jsonBuildObject } from 'kysely/helpers/postgres'
 
const result = await db
  .selectFrom('person')
  .select((eb) => [
    'id',
    jsonBuildObject({
      first: eb.ref('first_name'),
      last: eb.ref('last_name'),
      full: sql<string>`first_name || ' ' || last_name`
    }).as('name')
  ])
  .execute()
 
console.log(result[0]?.name.first)
console.log(result[0]?.name.last)
console.log(result[0]?.name.full)

The generated SQL (PostgreSQL):


select "id", json_build_object(
  'first', first_name,
  'last', last_name,
  'full', first_name || ' ' || last_name
) as "name"
from "person"

Nested Relations

You can nest jsonArrayFrom() and jsonObjectFrom() to fetch deeply nested data:


import { jsonArrayFrom, jsonObjectFrom } from 'kysely/helpers/postgres'
 
const result = await db
  .selectFrom('person')
  .select((eb) => [
    'id',
    'first_name',
    jsonArrayFrom(
      eb.selectFrom('pet')
        .select((eb) => [
          'pet.id',
          'pet.name',
          jsonObjectFrom(
            eb.selectFrom('toy')
              .select(['toy.id as toy_id', 'toy.name as toy_name'])
              .whereRef('toy.pet_id', '=', 'pet.id')
              .limit(1)
          ).as('favorite_toy')
        ])
        .whereRef('pet.owner_id', '=', 'person.id')
    ).as('pets')
  ])
  .execute()
 
console.log(result[0]?.pets[0]?.favorite_toy?.toy_name)

The generated SQL (PostgreSQL):


select "id", "first_name", (
  select coalesce(json_agg(agg), '[]') from (
    select
      "pet"."id",
      "pet"."name",
      (
        select to_json(obj) from (
          select "toy"."id" as "toy_id", "toy"."name" as "toy_name"
          from "toy"
          where "toy"."pet_id" = "pet"."id"
          limit $1
        ) as obj
      ) as "favorite_toy"
    from "pet"
    where "pet"."owner_id" = "person"."id"
  ) as agg
) as "pets"
from "person"

Performance Considerations

JSON aggregation happens in the database, so you fetch all related data in a single query. This is often faster than N+1 queries, but can be slower than separate queries for large datasets.

Pros:

Single roundtrip to the database
Atomic snapshot of data
No N+1 query problem

Cons:

Can produce large result sets
May be slower than separate queries for large datasets
Limited control over pagination of nested data

For large datasets, consider using separate queries with pagination or a dedicated GraphQL/REST API layer.

What’s Next

Expressions — Learn how to build complex expressions for filtering related rows.
Plugins — Use ParseJSONResultsPlugin when your dialect doesn’t parse JSON automatically.

Relations (JSON Nesting)

The Helpers

Fetching Arrays of Related Rows

Fetching a Single Related Row

Dialect Differences

PostgreSQL

MySQL

SQLite

ParseJSONResultsPlugin

The $notNull() Helper

Building JSON Objects from Expressions

Nested Relations

Performance Considerations

What’s Next

The `$notNull()` Helper