Skip to main content

​
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

Build docs developers (and LLMs) love

Get started for freeTalk to us