JSONPathBuilder

Type-safe builder for traversing JSON columns and creating JSON path expressions

JSONPathBuilder

JSONPathBuilder provides a type-safe way to traverse JSON structures and build JSON path expressions. It’s created by calling eb.ref(column, '->$') or eb.jsonPath<'column'>() on an ExpressionBuilder.

Type Parameters

S · any · required

The source type (root JSON type)

O · any · default: "S"

The current output type at this point in the traversal

Methods

at

Access an element of a JSON array in a specific location.

at<I extends number | 'last' | `#-${number}`>(
  index: I,
): TraversedJSONPathBuilder<S, O2>

index · number | 'last' | '#-{number}' · required

Array index, last for MySQL, or #-N for SQLite (e.g., #-1 for last element)

Since there’s no guarantee an element exists in the given array location, the resulting type is always nullable. If you’re sure the element exists, you should use $assertType to narrow the type safely.

See also key to access properties of JSON objects.

Example:

await db.selectFrom('person')
  .select(eb =>
    eb.ref('nicknames', '->').at(0).as('primary_nickname')
  )
  .execute()

The generated SQL (PostgreSQL):

select "nicknames"->0 as "primary_nickname" from "person"

Combined with key:

db.selectFrom('person').select(eb =>
  eb.ref('experience', '->').at(0).key('role').as('first_role')
)

The generated SQL (PostgreSQL):

select "experience"->0->'role' as "first_role" from "person"

MySQL with ‘last’:

db.selectFrom('person').select(eb =>
  eb.ref('nicknames', '->$').at('last').as('last_nickname')
)

The generated SQL (MySQL):

select `nicknames`->'$[last]' as `last_nickname` from `person`

SQLite with ’#-1’:

db.selectFrom('person').select(eb =>
  eb.ref('nicknames', '->>$').at('#-1').as('last_nickname')
)

The generated SQL (SQLite):

select "nicknames"->>'$[#-1]' as `last_nickname` from `person`

key

Access a property of a JSON object.

key<K extends keyof NonNullable<O> & string>(
  key: K,
): TraversedJSONPathBuilder<S, O2>

key · string · required

Object property key

If a field is optional, the resulting type will be nullable.

See also at to access elements of JSON arrays.

Example:

db.selectFrom('person').select(eb =>
  eb.ref('address', '->').key('city').as('city')
)

The generated SQL (PostgreSQL):

select "address"->'city' as "city" from "person"

Going deeper:

db.selectFrom('person').select(eb =>
  eb.ref('profile', '->$').key('website').key('url').as('website_url')
)

The generated SQL (MySQL):

select `profile`->'$.website.url' as `website_url` from `person`

Combined with at:

db.selectFrom('person').select(eb =>
  eb.ref('profile', '->').key('addresses').at(0).key('city').as('city')
)

The generated SQL (PostgreSQL):

select "profile"->'addresses'->0->'city' as "city" from "person"

TraversedJSONPathBuilder

Returned by at() and key() calls. Extends JSONPathBuilder with additional methods for aliasing and type casting.

as

Returns an aliased version of the expression.

as<A extends string>(alias: A): AliasedExpression<O, A>
as<A extends string>(alias: Expression<unknown>): AliasedExpression<O, A>

alias · string | Expression · required

The alias name for the expression

In addition to slapping as "the_alias" to the end of the SQL, this method also provides strict typing.

Example:

const result = await db
  .selectFrom('person')
  .select(eb =>
    eb.ref('address', '->').key('city').as('city')
  )
  .executeTakeFirstOrThrow()

// `city` field exists in the result type.
console.log(result.city)

$castTo

Change the output type of the json path.

$castTo<O2>(): TraversedJSONPathBuilder<S, O2>

O2 · type parameter · required

The new output type

This method call doesn’t change the SQL in any way. This methods simply returns a copy of this JSONPathBuilder with a new output type.

Example:

interface Address {
  street: string
  city: string
  zipCode: string
}

const result = await db
  .selectFrom('person')
  .select(eb =>
    eb.ref('data', '->').key('address').$castTo<Address>().as('address')
  )
  .execute()

$notNull

Omit null from the expression’s type.

$notNull(): TraversedJSONPathBuilder<S, Exclude<O, null>>

This function can be useful in cases where you know an expression can’t be null, but Kysely is unable to infer it.

This method call doesn’t change the SQL in any way. This methods simply returns a copy of this with a new output type.

toOperationNode

Converts the builder to an operation node for internal use.

toOperationNode(): OperationNode

AliasedJSONPathBuilder

Returned by as() calls. Represents a JSON path expression with an alias.

class AliasedJSONPathBuilder<O, A extends string>

O · any · required

The output type

A · string · required

The alias name

Usage Patterns

JSON Reference (PostgreSQL/SQLite)

Use -> operator for JSON references that chain operators:

// PostgreSQL
eb.ref('data', '->').key('user').key('name')
// Generates: "data"->'user'->'name'

eb.ref('items', '->').at(0).key('price')
// Generates: "items"->0->'price'

JSON Path (MySQL)

Use ->$ operator for JSON path expressions:

// MySQL
eb.ref('data', '->$').key('user').key('name')
// Generates: `data`->'$.user.name'

eb.ref('items', '->$').at(0).key('price')
// Generates: `items`->'$[0].price'

JSON Path Expression

Use jsonPath() for creating path expressions to use in functions:

await db.updateTable('person')
  .set('profile', (eb) => eb.fn('json_set', [
    'profile',
    eb.jsonPath<'profile'>().key('addresses').at('last').key('city'),
    eb.val('San Diego')
  ]))
  .where('id', '=', 3)
  .execute()

The generated SQL (MySQL):

update `person`
set `profile` = json_set(`profile`, '$.addresses[last].city', $1)
where `id` = $2

Type Safety

The JSONPathBuilder maintains full type safety when traversing JSON structures:

interface Database {
  person: {
    id: number
    profile: {
      name: string
      addresses: Array<{
        street: string
        city: string
        zipCode: string
      }>
    }
  }
}

// Type: string | null
const city = eb.ref('profile', '->').key('addresses').at(0).key('city')

// Type: string (if you're certain it exists)
const cityNotNull = eb.ref('profile', '->').key('addresses').at(0).key('city').$notNull()

The builder infers:

• Object property types from the schema
• Array element types
• Nullability based on optional fields and array access
• Proper return types at each traversal step