JSONPathBuilder

class JSONPathBuilder<S, O = S> {

constructor(node: JSONReferenceNode | JSONPathNode);

at<I extends any[] extends O ? number | "last" | `#-${number}` : never, O2 = null | NonNullable<NonNullable<O>[keyof NonNullable<O> & number]>>(index: `${I}` extends `${any}.${any}` | `#--${any}` ? never : I): TraversedJSONPathBuilder<S, O2>;

key<K extends any[] extends O ? never : O extends object ? keyof NonNullable<O> & string : never, O2 = undefined extends O ? null | NonNullable<NonNullable<O>[K]> : null extends O ? null | NonNullable<NonNullable<O>[K]> : string extends keyof NonNullable<O> ? null | NonNullable<NonNullable<O>[K]> : NonNullable<O>[K]>(key: K): TraversedJSONPathBuilder<S, O2>;

}

§Type Parameters

[src]

O = S

[src]

§Constructors

new JSONPathBuilder(node: JSONReferenceNode | JSONPathNode)

[src]

§Methods

[src]

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

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 {@link SelectQueryBuilder.$assertType} to narrow the type safely.

See also {@link key} to access properties of JSON objects.

Examples

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

The generated SQL (PostgreSQL):

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

Combined with {@link 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"

You can use 'last' to access the last element of the array in MySQL:

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`

Or '#-1' in SQLite:

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`

[src]

Access a property of a JSON object.

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

See also {@link at} to access elements of JSON arrays.

Examples

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 {@link 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"