import { kysely } from "https://deno.land/x/kysely_postgrs_js_dialect@v0.27.4/mod.ts";
const { JSONPathBuilder } = kysely;
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 SelectQueryBuilder.$assertType to narrow the type safely.
See also 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 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`
Parameters
index: `${I}` extends `${any}.${any}` | `#--${any}` ? never : I